public OracleTransformationProvider(Dialect dialect, string connectionString)
: base(dialect, connectionString)
{
connection = new OracleConnection();
connection.ConnectionString = base.connectionString;
connection.Open();
}
protected void btnSave_Click(object sender, EventArgs e)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
DataTable tbl = Data.Get_fdic_admin_assignment_data(ddlAdmin.SelectedValue, conn);
try
{
Process_Modify(tbl, conn);
process_remove(tbl, conn);
tx.Commit();
}
catch
{
tx.Rollback();
}
finally
{
SQLStatic.SQL.CloseConnection(conn);
}
ViewState["skip"] = false;
DrawGrid();
Bas_Utility.Misc.AlertSaved(Page);
}
//有返回值的SQL执行
public System.Data.Common.DbDataReader ExecuteReader(string cmdText)
{
cmdText = SqlTransfer(cmdText);
Oracle.DataAccess.Client.OracleDataReader reader;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connStr);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.Text;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(reader);
}
catch (Exception exp)
{
LogWriter.Error(exp, cmdText);
throw exp;
}
}
private void Save()
{
// DataTable tbl=null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_trn_budget.add_extra_amount", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["Budget_Employee"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", opnlstBudgetYear.SelectedItem.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "total_amount_", txtAmount.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", txtReason.Text);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
public static void Save_er_property(string account_number, string code, string value, string user_name, string AllDivisions,
Oracle.DataAccess.Client.OracleConnection conn)
{
if (AllDivisions.Equals("3"))
{
account_number = account_number.Substring(0, 7) + "-0000-000";
AllDivisions = "1";
}
ArrayList al = new ArrayList(3);
al.Add(SQLStatic.StoredProcedure.OneParamerer("account_number_", "in", "varchar2", account_number));
al.Add(SQLStatic.StoredProcedure.OneParamerer("code_", "in", "varchar2", code));
al.Add(SQLStatic.StoredProcedure.OneParamerer("value_", "in", "varchar2", value));
al.Add(SQLStatic.StoredProcedure.OneParamerer("user_name_", "in", "varchar2", user_name));
al.Add(SQLStatic.StoredProcedure.OneParamerer("AllDivisions_", "in", "varchar2", AllDivisions));
if (conn != null)
{
SQLStatic.StoredProcedure.ExecuteNonQueryProcedure("pkg_newhirewizard.Save_er_property", al, conn);
}
else
{
SQLStatic.StoredProcedure.ExecuteNonQueryProcedure("pkg_newhirewizard.Save_er_property", al);
}
}
private DataTable GetGridTable()
{
string strProcedureName = "BASDBA.pkg_training.CommunicationList";
if (ViewState["Product_Code"].ToString() == "11001")
{
strProcedureName = "BASDBA.pkg_training_cdp.CommunicationList";
}
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", -1);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "msg_list", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
protected void Page_Load(object sender, EventArgs e)
{
#region BasTemplate
if (!IsPostBack)
{
Template.BasTemplate objBasTemplate = new Template.BasTemplate();
try
{
if (Request.Cookies["Session_ID"] == null)
{
Response.Redirect("/web_projects/login_error/ErrorPage.aspx?error=Cookie not found. Please signon first", false);
}
string strResult = objBasTemplate.Authenticate(Request.Cookies["Session_ID"].Value.ToString(), Request.Url.Authority.ToString(), Request.Url.AbsolutePath.ToString(), true, true);
if (strResult != "")
{
Response.Redirect("/web_projects/login_error/ErrorPage.aspx?error=" + strResult, false);
return;
}
objBasTemplate.SetSeatchField(0);
objBasTemplate.ShowNotes = false;
LblTemplateHeader2.Text = objBasTemplate.Header2();
ViewState["AccessType"] = objBasTemplate.strAccessType;
ViewState["Employee_Number"] = objBasTemplate.strEmployee_Number;
ViewState["Processing_Year"] = objBasTemplate.strProcessingYear;
ViewState["Role_Restriction_Level"] = objBasTemplate.strRole_Restriction_Level;
ViewState["Selected_Account_Number"] = objBasTemplate.strSelected_Account_Number;
ViewState["Selected_Employee_Class_Code"] = objBasTemplate.strSelected_Employee_Class_Code;
ViewState["User_Group_ID"] = objBasTemplate.strUser_Group_ID;
ViewState["User_ID"] = objBasTemplate.strUser_ID;
ViewState["User_Name"] = objBasTemplate.strUser_Name;
ViewState["User_Primary_Role"] = objBasTemplate.strUser_Primary_Role;
// Wizard
string strpnlXML = objBasTemplate.PanelXML();
if (strpnlXML != "")
{
if (strpnlXML.IndexOf("Error:") != -1)
{
Response.Redirect("/web_projects/login_error/ErrorPage.aspx?error=" + strpnlXML, true);
return;
}
ViewState["CurrGrp"] = objBasTemplate.Wizard_Value("CurrGrp");
ViewState["CurrGrpTtl"] = objBasTemplate.Wizard_Value("CurrGrpTtl");
ViewState["CurrGrpUrl"] = objBasTemplate.Wizard_Value("CurrGrpUrl");
ViewState["CurrStp"] = objBasTemplate.Wizard_Value("CurrStp");
ViewState["CurrStpTtl"] = objBasTemplate.Wizard_Value("CurrStpTtl");
ViewState["CurrStpUrl"] = objBasTemplate.Wizard_Value("CurrStpUrl");
ViewState["Is_Step_Completed"] = objBasTemplate.Wizard_Value("Is_Step_Completed");
ViewState["NextGrp"] = objBasTemplate.Wizard_Value("NextGrp");
ViewState["NextGrpTtl"] = objBasTemplate.Wizard_Value("NextGrpTtl");
ViewState["NextGrpUrl"] = objBasTemplate.Wizard_Value("NextGrpUrl");
ViewState["NextStp"] = objBasTemplate.Wizard_Value("NextStp");
ViewState["NextStpTtl"] = objBasTemplate.Wizard_Value("NextStpTtl");
ViewState["NextStpUrl"] = objBasTemplate.Wizard_Value("NextStpUrl");
ViewState["NoGrp"] = objBasTemplate.Wizard_Value("NoGrp");
ViewState["NoStpInGrp"] = objBasTemplate.Wizard_Value("NoStpInGrp");
ViewState["PrvGrp"] = objBasTemplate.Wizard_Value("PrvGrp");
ViewState["PrvGrpTtl"] = objBasTemplate.Wizard_Value("PrvGrpTtl");
ViewState["PrvGrpUrl"] = objBasTemplate.Wizard_Value("PrvGrpUrl");
ViewState["PrvStp"] = objBasTemplate.Wizard_Value("PrvStp");
ViewState["PrvStpTtl"] = objBasTemplate.Wizard_Value("PrvStpTtl");
ViewState["PrvStpUrl"] = objBasTemplate.Wizard_Value("PrvStpUrl");
}
}
catch (Exception ex)
{
string strError = "Error Message: " + ex.Message + "~~Application:" + ex.Source + "~~Method:" + ex.TargetSite + "~~Detail:" + ex.StackTrace;
Response.Redirect("/web_projects/login_error/ErrorPage.aspx?error=" + strError.Replace("\n", "~"));
}
finally
{
objBasTemplate.CleanUp();
objBasTemplate.Dispose();
}
}
#endregion
ProcessCommad();
if (!string.IsNullOrEmpty(hidCommand.Value))
{
if (hidCommand.Value.Equals("DoLoad"))
{
hidCommand.Value = string.Empty;
LoadImage();
return;
}
if (hidCommand.Value.Equals("DoSave"))
{
hidCommand.Value = string.Empty;
//btnSave_Click(null, null);
DoSave();
return;
}
}
btnLoad.Attributes.Add("onclick", "Do_Load()");
btnSave.Attributes.Add("onclick", "Do_Save()");
if (!IsPostBack)
{
ViewState["Employee_Number"] = Data.UsedEmployeeNumber2(ViewState["Employee_Number"].ToString(), Request.Cookies["Session_ID"].Value.ToString());
Bas_Utility.Utilities.SetValidators(this.Page);
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
try
{
ViewState["Book"] = SQLStatic.Sessions.GetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "BookRequest", conn);
ViewState["Request_Record_ID"] = SQLStatic.Sessions.GetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "Request_Record_ID", conn);
ViewState["AppStatus"] = Data.ApplicationStatus(ViewState["Request_Record_ID"].ToString());
ViewState["Product_Code"] = SQLStatic.Sessions.GetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "Product_Code", conn);
lblBalance.Text = Data.AvailableAmount(ViewState["Employee_Number"].ToString(), ViewState["Processing_Year"].ToString(), conn);
lblCourseTitle.Text = Data.CourseName(ViewState["Request_Record_ID"].ToString(), conn);
}
finally
{
conn.Close();
conn.Dispose();
}
RadTabStrip1.Tabs[0].Selected = true;
RadTabStrip1_TabClick(null, null);
SetHeaderInformation();
Data.FillBudgetYears(ddlBudgetYear, lblBalance, ViewState["Employee_Number"].ToString(), ViewState["Processing_Year"].ToString());
ProcessesStarFunctionality();
SetupWizardMenu();
}
}
public static string CourseName(string record_id, Oracle.DataAccess.Client.OracleConnection conn)
{
return(SQLStatic.SQL.ExecScaler("select PKG_Training.CourseCodeTitle(" + record_id + ") from dual", conn).ToString());
}
private void GetExistingData()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.GetExpenseList", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", Request.Params["expid"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "expenses_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
string strPay_to = "";
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
foreach (DataRow dr in tbl.Rows)
{
SetValue(txtExpenseName, dr["expense_type"].ToString());
double dbAmount = Convert.ToDouble(dr["amount"].ToString());
if (dbAmount < 0)
{
dbAmount = -dbAmount;
}
SetValue(txtAmount, dbAmount.ToString());
SetValue(txtDescription, dr["note"].ToString());
// ListItem li0 = new ListItem("--Select--"," ");
// ddlExpenseType.Items.Add(li0);
if (dr["expense_type_ID"].ToString() != "1000")
{
ListItem li = new ListItem(txtExpenseName.Text, dr["expense_type_ID"].ToString());
ddlExpenseType.Items.Add(li);
}
SetValue(ddlExpenseType, dr["expense_type_ID"].ToString());
for (int i = 0; i < ddlExpenseType.Items.Count; i++)
{
if (ddlExpenseType.Items[i].Value == dr["expense_type_ID"].ToString())
{
SetValue(txtExpenseType, ddlExpenseType.Items[i].Text);
ddlExpenseType.SelectedIndex = i;
break;
}
}
SetValue(txtVedorName, dr["vendor_name"].ToString());
SetValue(txtVendorContact, dr["vendor_contact_name"].ToString());
SetValue(txtAddressLine1, dr["vendor_address_1"].ToString());
SetValue(txtAddressLine2, dr["vendor_address2"].ToString());
SetValue(txtCity, dr["vendor_city"].ToString());
SetValue(ddlStates, dr["vendor_state"].ToString());
SetValue(txtZipCode, dr["vendor_zip"].ToString());
SetValue(txtPhoneNumber, dr["vendor_phone"].ToString());
SetValue(txtFaxNumber, dr["vendor_fax"].ToString());
SetValue(txtemail, dr["vendor_email"].ToString());
SetValue(txtProvince, dr["province"].ToString());
SetValue(ddlContries, dr["country_code"].ToString());
strPay_to = dr["pay_to"].ToString();
// for (int i=0;i<ddlStates.Items.Count;i++)
// if (ddlStates.Items[i].Value==strState)
// {
// ddlStates.SelectedIndex=i;
// break;
// }
txtState.Text = ddlStates.SelectedItem.Text;
ddlExpenseType_SelectedIndexChanged(null, null);
ddlContries_SelectedIndexChanged(null, null);
SetValue(txtExpenseName, dr["expense_type"].ToString());
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
tbl.Dispose();
}
if (strPay_to != "")
{
optlstVendor.SelectedIndex = Convert.ToInt32(strPay_to);
optlstVendor_SelectedIndexChanged(null, null);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
try
{
string strInOPenEE = "0";
string newCatePlN = Request.Params["catplan"];
if (cbOe.Checked)
{
strInOPenEE = "1";
}
Data.Save_In_OE(Request.Params["accnt"], Request.Params["py"], strInOPenEE, Request.Params["catcode"],
Request.Params["catplan"], Request.Params["class"], Request.Params["batch"], conn);
if (!Request.Params["action"].Equals("0"))
{
newCatePlN = Data.Replace_coverage(Request.Params["accnt"], Request.Params["ver"], Request.Params["class"], Request.Params["catcode"],
Request.Params["catplan"], Request.Params["py"], Request.Params["batch"], Request.Params["ratetype"], conn);
}
if (!string.IsNullOrEmpty(hidcomVales.Value))
{
// remove first seperatoe
hidcomVales.Value = hidcomVales.Value.Remove(0, 1);
ArrayList ar = new ArrayList(hidcomVales.Value.Split(new char[] { '!' }));
string long_description = Request.Params["cvrg"];
if (!Request.Params["cvrg"].IndexOf(" - ").Equals(-1))
{
long_description = Request.Params["cvrg"].Substring(0, Request.Params["cvrg"].IndexOf(" - "));
}
string strFamilyStatus = string.Empty;
foreach (string str in ar)
{
string[] fs = str.Split('=');
strFamilyStatus = fs[0].Substring(0, 3);
fs[0] = fs[0].Substring(3);
string[] age = fs[0].Split('-');
age[0] = age[0].Replace(" + ", "");
Data.SaveRate_ver1(Request.Params["accnt"], Request.Params["ver"], Request.Params["class"], Request.Params["catcode"],
newCatePlN, strFamilyStatus, Request.Params["py"], Request.Params["batch"], fs[1], age[0].Trim(), "", "", long_description, fs[0], "1", conn);
}
}
if (!string.IsNullOrEmpty(hidcomVale2.Value))
{
// remove first seperatoe
hidcomVale2.Value = hidcomVale2.Value.Remove(0, 1);
ArrayList ar = new ArrayList(hidcomVale2.Value.Split(new char[] { '!' }));
string long_description = Request.Params["cvrg"];
if (!Request.Params["cvrg"].IndexOf(" - ").Equals(-1))
{
long_description = Request.Params["cvrg"].Substring(0, Request.Params["cvrg"].IndexOf(" - "));
}
foreach (string str in ar)
{
string[] fs = str.Split('=');
strFamilyStatus = fs[0].Substring(0, 3);
fs[0] = fs[0].Substring(3);
string[] age = fs[0].Split('-');
age[0] = age[0].Replace(" + ", "");
age[0] = age[0].Replace("_a", "");
Data.SaveRate_ver1(Request.Params["accnt"], Request.Params["ver"], Request.Params["class"], Request.Params["catcode"],
newCatePlN, strFamilyStatus, Request.Params["py"], Request.Params["batch"], fs[1], age[0].Trim(), "", "", long_description, fs[0], "2", conn);
}
}
tx.Commit();
}
catch
{
tx.Rollback();
throw;
}
finally
{
SQLStatic.SQL.CloseConnection(conn);
}
btnCancel_Click(null, null);
}
protected void btnSave_Click(object sender, EventArgs e)
{
DataTable tbl = gvProfile.DataSource as DataTable;
if (tbl == null)
{
tbl = GetData();
}
RadioButtonList rblst = null;
string strRecord_id = "";
int indx = 0;
foreach (DataRow dr in tbl.Rows)
{
//rblst = FindControl( "rblst_" + indx.ToString()) as RadioButtonList ;
rblst = Bas_Utility.Utilities.GetRadioButtonList(Page, "rblst_" + indx.ToString());
indx++;
strRecord_id = rblst.ID.Replace("rblst_", "");
if (rblst.SelectedValue.Equals("1"))
{
}
else if (rblst.SelectedValue.Equals("0"))
{
}
else
{
jscriptsFunctions.Misc.Alert(Page, "A Decision must be made for ALL items");
return;
}
}
strRecord_id = "";
indx = 0;
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
try
{
foreach (DataRow dr in tbl.Rows)
{
//rblst = FindControl( "rblst_" + indx.ToString()) as RadioButtonList;
rblst = Bas_Utility.Utilities.GetRadioButtonList(Page, "rblst_" + indx.ToString());
indx++;
strRecord_id = rblst.ID.Replace("rblst_", "");
if (rblst.SelectedValue.Equals("1"))
{
if (dr["which"].ToString().Equals("a"))
{
processAddress(dr);
}
else
{
processProfile(dr);
}
ViewState["email_apprve"] = "0";
}
//else if (rblst.SelectedValue.Equals("0"))
//{
//}
//else
//{
// //tx.Rollback();
// //string strError = "<script>alert('You must ckeck all the dependents first')</script>";
// //Page.ClientScript.RegisterStartupScript(Page.GetType(), "strError", strError);
// //return;
//}
}
Data.Close_pending_ee_profile_addrs(session_id, ViewState["EE_Number"].ToString());
tx.Commit();
}
catch
{
tx.Rollback();
throw;
}
finally
{
SQLStatic.SQL.CloseConnection(conn);
}
jscriptsFunctions.Misc.AlertSaved(Page);
btnNext_Click(null, null);
//Response.Redirect("Profile.aspx?SkipCheck=YES&ee=" + Request.Params["ee"], true);
}
public string GetDocumentPath(string referenceNumber, int documentType, string mimeType)
{
using (var connection = new Oracle.DataAccess.Client.OracleConnection(DataContextConnectionString))
{
var infringementDocumentType =
new InfringementDocumentType
{
REFERENCE_NUMBER = referenceNumber,
DOCUMENT_TYPE_ID = documentType,
MIME_TYPE = mimeType
};
using (var command = new Oracle.DataAccess.Client.OracleCommand())
{
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.Parameters.Add(
new Oracle.DataAccess.Client.OracleParameter("P_Infringement_Document", Oracle.DataAccess.Client.OracleDbType.Object)
{
Value = infringementDocumentType,
UdtTypeName = "ITS.INFRINGEMENT_DOCUMENT_TYPE"
});
command.Parameters.Add("O_RESULT", Oracle.DataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
ExcecuteNonQuery(command, "ITS.OFFENCE_DOCUMENTS.Submit_Infringement_Document", connection);
if ((command.Parameters["O_RESULT"].Value is DBNull))
{
throw new Exception("Failed to return valid file paths.");
}
var refCursor = (Oracle.DataAccess.Types.OracleRefCursor)command.Parameters["O_RESULT"].Value;
using (var dataReader = refCursor.GetDataReader())
{
while (dataReader.Read())
{
if (dataReader["MIME_DATA_PATH"] is DBNull || dataReader["FILENAME"] is DBNull)
{
continue;
}
var filePath = dataReader["MIME_DATA_PATH"] as string;
var fileName = dataReader["FILENAME"] as string;
return(Path.Combine(filePath, fileName));
}
}
}
finally
{
foreach (Oracle.DataAccess.Client.OracleParameter parameter in command.Parameters)
{
if (parameter.Value is IDisposable)
{
((IDisposable)(parameter.Value)).Dispose();
}
parameter.Dispose();
}
}
return(string.Empty);
}
}
}
public Northwind CreateDB(System.Data.ConnectionState state)
{
CheckRecreateSqlite();
XSqlConnection conn = new XSqlConnection(connStr);
if (state == System.Data.ConnectionState.Open)
conn.Open();
Northwind db = new Northwind(conn);
return db;
}
public static DbConnection GetConnection(string connectionString, string providerName)
{
DbConnection conn = null;
switch (providerName)
{
case "System.Data.SqlClient": conn = new SqlConnection(connectionString); break;
case "Oracle.DataAccess.Client": conn = new Oracle.DataAccess.Client.OracleConnection(connectionString); break;
case "System.Data.OracleClient": conn = new OracleConnection(connectionString); break;
case "System.Data.SQLite": conn = new SQLiteConnection(connectionString); break;
}
return conn;
}
public override int Execute(DbCommand[] cmdArray)
{
var connection = new Oracle.DataAccess.Client.OracleConnection(connectionString);
Oracle.DataAccess.Client.OracleTransaction transaction = null;
int r = 0;
try
{
connection.Open();
transaction = connection.BeginTransaction();
for (int i = 0; i < cmdArray.Length; i++)
{
if (!(cmdArray[i] is Oracle.DataAccess.Client.OracleCommand))
throw new Exception("Not a Valid SQL Command");
var command = (Oracle.DataAccess.Client.OracleCommand)cmdArray[i];
SetTimeOut(command);
command.Connection = connection;
command.Transaction = transaction;
foreach (Oracle.DataAccess.Client.OracleParameter parameter in command.Parameters)
{
if (parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
}
r += command.ExecuteNonQuery();
}
transaction.Commit();
return r;
}
catch (Exception ex)
{
if (transaction != null) transaction.Rollback();
string exceptionMessage = GetOracleExceptionMessage(ex);
throw new Exception(exceptionMessage);
}
finally
{
connection.Close();
}
}
public static void SaveEffectiveDate(string strRecID, string strEffectiveDate, Oracle.DataAccess.Client.OracleConnection conn)
{
ArrayList al = new ArrayList(2);
al.Add(SQLStatic.StoredProcedure.OneParamerer("rqst_dep_no_record_id_", "in", "varchar2", strRecID));
al.Add(SQLStatic.StoredProcedure.OneParamerer("effective_date_", "in", "varchar2", strEffectiveDate));
SQLStatic.StoredProcedure.ExecuteNonQueryProcedure("pkg_employee_maintenance.update_effective_date", al, conn);
}
public JsonResult Create(int noticeId)
{
BusinessCheckNotice businessCheckNotice = RepositoryFactory.GetRepository<IBusinessCheckNoticeRepository, BusinessCheckNotice>().FindBy(noticeId);
if (businessCheckNotice == null)
{
return Json(new { success = false, message = "商检单不存在" });
}
PipeIncomeRecord incomeRecord = repository.FindBy(businessCheckNotice);
if (incomeRecord != null)
{
return Json(new { success = false, message = "已制作了该商检任务的入库单，不能重量制作" });
}
string code = Request.Form["Code"];
string unit = Request.Form["Unit"];
string strPipes = Request.Form["Pipes"];
if (string.IsNullOrEmpty(code))
{
return Json(new { success = false, message = "编码不能为空" });
}
incomeRecord = repository.FindBy(code);
if (incomeRecord != null)
{
return Json(new { success = false, message = "编号已被占用，请尝试使用其他编号" });
}
businessCheckNotice.Waggons = RepositoryFactory.GetRepository<IDeliverWaggonRepository, DeliverWaggon>().FindBy(businessCheckNotice);
#region scarface
// IList<PDCPMS.Model.Common.Pipe> pipes = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>().FindBy(businessCheckNotice.Waggons);
#endregion
JavaScriptSerializer serializer = new JavaScriptSerializer();
IList<PDCPMS.DataContracts.Common.Pipe> pipeContracts = serializer.Deserialize<IList<PDCPMS.DataContracts.Common.Pipe>>(strPipes);
IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork);
IPipePositionRepository posRepository = RepositoryFactory.GetRepository<IPipePositionRepository, PipePosition>();
incomeRecord = new PipeIncomeRecord(0, code, businessCheckNotice, UserSession.OnlineAccount, DateTime.Now);
incomeRecord.State = PipeIncomeRecordState.Created;
repository.Add(incomeRecord);
unitOfWork.Commit();
#region scarface
//for (int i = pipes.Count - 1; i > -1; i--)
//{
// PDCPMS.Model.Common.Pipe pipe = pipes[i];
// PDCPMS.DataContracts.Common.Pipe pc = pipeContracts.FirstOrDefault(p => p.Key == (long)pipe.Key);
// if (pc != null)
// {
// if (pc.BusinessLength != 0)
// {
// pipe.BusinessLength = pc.BusinessLength;
// }
// else
// {
// pipe.BusinessLength = new float?();
// }
// if (pc.Weight != 0)
// {
// pipe.Weight = pc.Weight;
// }
// else
// {
// pipe.Weight = new float?();
// }
// //if ((pipe.State & PipeState.NormalChecking) == PipeState.NormalChecking) // 已发起了常检则不允许更改垛位
// //{
// //}
// //else
// //{
// if (pc.PositionId != 0)
// {
// pipe.Position = posRepository.FindBy(pc.PositionId);
// }
// //}
// }
// pipe.State |= PipeState.IncomeRecordDoing;
// pipe.PipeIncomeRecord = incomeRecord;
// pipeRepository[pipe.Key] = pipe;
//};
//incomeRecord.TotalCount = businessCheckNotice.ReceiveCount;
//incomeRecord.TotalLength = pipes.Sum(p => p.BusinessLength.HasValue ? p.BusinessLength.Value : 0);
//incomeRecord.TotalWeight = pipes.Sum(p => p.Weight.HasValue ? p.Weight.Value : 0) / 1000;
#endregion
#region 2010-11-12 scarface 新增代码
//获取前台Json的值遍历生产SQL
StringBuilder sb = new StringBuilder();
DateTime now = DateTime.Now;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString);
CY.Utility.DBUtility.OracleUtility db = new CY.Utility.DBUtility.OracleUtility(conn);
conn.Open();
Oracle.DataAccess.Client.OracleTransaction tran = conn.BeginTransaction();
try
{
sb.Append(" update Pipe set PipeIncomeRecordId = " + incomeRecord.Key + ",");
sb.Append(string.Format(" state = (state + {0} - bitand(state,{0})),", (int)PipeState.IncomeRecordDoing));
sb.Append(" PosLastChangeTime = to_date('" + now + "', 'yyyy-mm-dd HH24:mi:ss') ");
IList<DeliverWaggon> waggons = businessCheckNotice.Waggons;
if (waggons == null)
{
throw new ArgumentNullException("waggons");
}
string waggonIds = string.Empty;
for (int i = waggons.Count - 1; i > -1; i--)
{
waggonIds += OracleDataHelper.GetSqlValue(waggons[i].Key);
if (i != 0)
{
waggonIds += ",";
}
}
sb.Append(string.Format(" Where DeliverWaggonId in ({0})", waggonIds));
db.ExecuteSql(sb.ToString());
for (int i = pipeContracts.Count - 1; i > -1; i--)
{
sb = new StringBuilder();
sb.Append(" UPDATE Pipe SET ");
sb.Append(" BusinessLength = " + pipeContracts[i].BusinessLength + ",");
sb.Append(" Weight = " + pipeContracts[i].Weight + ",");
sb.Append(" PosLastChangeTime = to_date('" + now + "', 'yyyy-mm-dd HH24:mi:ss'),");
sb.Append(" PositionId = " + pipeContracts[i].PositionId);
sb.Append(" WHERE PipeId = " + pipeContracts[i].Key + "");
db.ExecuteSql(sb.ToString());
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return Json(new { success = false, message = "管子信息保存出错！"+ex });
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
//查询并统计数据
sb = new StringBuilder();
sb.Append(" SELECT SUM(BUSINESSLENGTH) AS lh ,SUM(WEIGHT)/1000 AS wt,count(*) as c FROM Pipe WHERE PIPEINCOMERECORDID = " + incomeRecord.Key + "");
DataTable dt = pipeRepository.ExcuteDs(sb.ToString());
float tl = dt.Rows[0]["lh"].ToString() == "" ? 0 : float.Parse(dt.Rows[0]["lh"].ToString());
float tw = dt.Rows[0]["wt"].ToString() == "" ? 0 : float.Parse(dt.Rows[0]["wt"].ToString());
int tc = dt.Rows[0]["c"].ToString() == "" ? 0 : int.Parse(dt.Rows[0]["c"].ToString());
sb = new StringBuilder();
sb.Append(" UPDATE Pipeincomerecord SET totalcount=" + tc + ",TOTALLENGTH = " + tl + ",TOTALWEIGHT = " + tw + " WHERE PIPEINCOMERECORDID = " + incomeRecord.Key + "");
//只执行一次查询 就更新数据库表
pipeRepository.Execute(sb.ToString());
#endregion
TempData["HintMessage"] = new PDCPMS.Application.HintMessage { Content = "保存成功" };
return Json(new { success = true, message = "保存成功", recordId = incomeRecord.Key });
}
public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "TICKET_NUMBER", TICKET_NUMBER);
OracleUdt.SetValue(con, pUdt, "PERSON_INFO_ID", PERSON_INFO_ID);
OracleUdt.SetValue(con, pUdt, "TITLE", TITLE);
OracleUdt.SetValue(con, pUdt, "FIRST_NAME", FIRST_NAME);
OracleUdt.SetValue(con, pUdt, "MIDDLE_NAMES", MIDDLE_NAMES);
OracleUdt.SetValue(con, pUdt, "SURNAME", SURNAME);
OracleUdt.SetValue(con, pUdt, "INITIALS", INITIALS);
OracleUdt.SetValue(con, pUdt, "IDENTIFICATION_NUMBER", IDENTIFICATION_NUMBER);
OracleUdt.SetValue(con, pUdt, "IDENTIFICATION_TYPE_ID", IDENTIFICATION_TYPE_ID);
OracleUdt.SetValue(con, pUdt, "IDENTIFICATION_COUNTRY_ID", IDENTIFICATION_COUNTRY_ID);
OracleUdt.SetValue(con, pUdt, "CITIZEN_TYPE_ID", CITIZEN_TYPE_ID);
OracleUdt.SetValue(con, pUdt, "GENDER", GENDER);
OracleUdt.SetValue(con, pUdt, "AGE", AGE);
OracleUdt.SetValue(con, pUdt, "BIRTHDATE", BIRTHDATE);
OracleUdt.SetValue(con, pUdt, "OCCUPATION", OCCUPATION);
OracleUdt.SetValue(con, pUdt, "TELEPHONE", TELEPHONE);
OracleUdt.SetValue(con, pUdt, "MOBILE_NUMBER", MOBILE_NUMBER);
OracleUdt.SetValue(con, pUdt, "FAX", FAX);
OracleUdt.SetValue(con, pUdt, "EMAIL", EMAIL);
OracleUdt.SetValue(con, pUdt, "COMPANY", COMPANY);
OracleUdt.SetValue(con, pUdt, "BUSINESS_TELEPHONE", BUSINESS_TELEPHONE);
OracleUdt.SetValue(con, pUdt, "PHYSICAL_ADDRESS_INFO_ID", PHYSICAL_ADDRESS_INFO_ID);
OracleUdt.SetValue(con, pUdt, "PHYSICAL_ADDRESS_TYPE_ID", PHYSICAL_ADDRESS_TYPE_ID);
OracleUdt.SetValue(con, pUdt, "PHYSICAL_STREET_1", PHYSICAL_STREET_1);
OracleUdt.SetValue(con, pUdt, "PHYSICAL_STREET_2", PHYSICAL_STREET_2);
OracleUdt.SetValue(con, pUdt, "PHYSICAL_SUBURB", PHYSICAL_SUBURB);
OracleUdt.SetValue(con, pUdt, "PHYSICAL_TOWN", PHYSICAL_TOWN);
OracleUdt.SetValue(con, pUdt, "PHYSICAL_CODE", PHYSICAL_CODE);
OracleUdt.SetValue(con, pUdt, "POSTAL_ADDRESS_INFO_ID", POSTAL_ADDRESS_INFO_ID);
OracleUdt.SetValue(con, pUdt, "POSTAL_ADDRESS_TYPE_ID", POSTAL_ADDRESS_TYPE_ID);
OracleUdt.SetValue(con, pUdt, "POSTAL_PO_BOX", POSTAL_PO_BOX);
OracleUdt.SetValue(con, pUdt, "POSTAL_STREET", POSTAL_STREET);
OracleUdt.SetValue(con, pUdt, "POSTAL_SUBURB", POSTAL_SUBURB);
OracleUdt.SetValue(con, pUdt, "POSTAL_TOWN", POSTAL_TOWN);
OracleUdt.SetValue(con, pUdt, "POSTAL_CODE", POSTAL_CODE);
OracleUdt.SetValue(con, pUdt, "OFFENCE_LOCATION_STREET", OFFENCE_LOCATION_STREET);
OracleUdt.SetValue(con, pUdt, "OFFENCE_LOCATION_SUBURB", OFFENCE_LOCATION_SUBURB);
OracleUdt.SetValue(con, pUdt, "OFFENCE_LOCATION_TOWN", OFFENCE_LOCATION_TOWN);
OracleUdt.SetValue(con, pUdt, "OFFENCE_LOCATION_LATITUDE", OFFENCE_LOCATION_LATITUDE);
OracleUdt.SetValue(con, pUdt, "OFFENCE_LOCATION_LONGITUDE", OFFENCE_LOCATION_LONGITUDE);
OracleUdt.SetValue(con, pUdt, "VEHICLE_REGISTRATION_MAIN", VEHICLE_REGISTRATION_MAIN);
OracleUdt.SetValue(con, pUdt, "VEHICLE_REGISTRATION_NO_2", VEHICLE_REGISTRATION_NO_2);
OracleUdt.SetValue(con, pUdt, "VEHICLE_REGISTRATION_NO_3", VEHICLE_REGISTRATION_NO_3);
OracleUdt.SetValue(con, pUdt, "VEHICLE_MAKE_MAIN", VEHICLE_MAKE_MAIN);
OracleUdt.SetValue(con, pUdt, "VEHICLE_MODEL_MAIN", VEHICLE_MODEL_MAIN);
OracleUdt.SetValue(con, pUdt, "VEHICLE_TYPE_MAIN", VEHICLE_TYPE_MAIN);
OracleUdt.SetValue(con, pUdt, "VEHICLE_LICENCE_EXPIRY_DATE", VEHICLE_LICENCE_EXPIRY_DATE);
OracleUdt.SetValue(con, pUdt, "VEHICLE_COLOUR", VEHICLE_COLOUR);
OracleUdt.SetValue(con, pUdt, "VEHICLE_REGISTER_NUMBER", VEHICLE_REGISTER_NUMBER);
OracleUdt.SetValue(con, pUdt, "VEHICLE_ENGINE_NUMBER", VEHICLE_ENGINE_NUMBER);
OracleUdt.SetValue(con, pUdt, "VEHICLE_CHASSIS_NUMBER", VEHICLE_CHASSIS_NUMBER);
OracleUdt.SetValue(con, pUdt, "GUARDIAN", GUARDIAN);
OracleUdt.SetValue(con, pUdt, "DIRECTION", DIRECTION);
OracleUdt.SetValue(con, pUdt, "METER_NUMBER", METER_NUMBER);
OracleUdt.SetValue(con, pUdt, "CASE_NUMBER", CASE_NUMBER);
OracleUdt.SetValue(con, pUdt, "CC_NUMBER", CC_NUMBER);
OracleUdt.SetValue(con, pUdt, "CHARGE_CODE_1", CHARGE_CODE_1);
OracleUdt.SetValue(con, pUdt, "CHARGE_CODE_1_ID", CHARGE_CODE_1_ID);
OracleUdt.SetValue(con, pUdt, "AMOUNT_1", AMOUNT_1);
OracleUdt.SetValue(con, pUdt, "CHARGE_CODE_2", CHARGE_CODE_2);
OracleUdt.SetValue(con, pUdt, "CHARGE_CODE_2_ID", CHARGE_CODE_2_ID);
OracleUdt.SetValue(con, pUdt, "AMOUNT_2", AMOUNT_2);
OracleUdt.SetValue(con, pUdt, "CHARGE_CODE_3", CHARGE_CODE_3);
OracleUdt.SetValue(con, pUdt, "CHARGE_CODE_3_ID", CHARGE_CODE_3_ID);
OracleUdt.SetValue(con, pUdt, "AMOUNT_3", AMOUNT_3);
OracleUdt.SetValue(con, pUdt, "HAS_ALTERNATIVE_CHARGE", HAS_ALTERNATIVE_CHARGE);
OracleUdt.SetValue(con, pUdt, "OFFENCE_DATE", OFFENCE_DATE);
OracleUdt.SetValue(con, pUdt, "COURT_DATE", COURT_DATE);
OracleUdt.SetValue(con, pUdt, "COURT_NAME", COURT_NAME);
OracleUdt.SetValue(con, pUdt, "COURT_ROOM", COURT_ROOM);
OracleUdt.SetValue(con, pUdt, "DISTRICT_NAME", DISTRICT_NAME);
OracleUdt.SetValue(con, pUdt, "PAYMENT_PLACE", PAYMENT_PLACE);
OracleUdt.SetValue(con, pUdt, "PAYMENT_DATE", PAYMENT_DATE);
OracleUdt.SetValue(con, pUdt, "OFFICER_CREDENTIAL_ID", OFFICER_CREDENTIAL_ID);
OracleUdt.SetValue(con, pUdt, "CAPTURED_DATE", CAPTURED_DATE);
OracleUdt.SetValue(con, pUdt, "CAPTURED_CREDENTIAL_ID", CAPTURED_CREDENTIAL_ID);
OracleUdt.SetValue(con, pUdt, "LICENCE_CODE", LICENCE_CODE);
OracleUdt.SetValue(con, pUdt, "LICENCE_TYPE", LICENCE_TYPE);
OracleUdt.SetValue(con, pUdt, "DRIVER_LICENCE_CERTIFICATE_NO", DRIVER_LICENCE_CERTIFICATE_NO);
OracleUdt.SetValue(con, pUdt, "MODIFIED_DATE", MODIFIED_DATE);
OracleUdt.SetValue(con, pUdt, "MODIFIED_CREDENTIAL_ID", MODIFIED_CREDENTIAL_ID);
OracleUdt.SetValue(con, pUdt, "SPEED", SPEED);
OracleUdt.SetValue(con, pUdt, "MASS_PERCENTAGE", MASS_PERCENTAGE);
OracleUdt.SetValue(con, pUdt, "IS_CANCELLED", IS_CANCELLED);
OracleUdt.SetValue(con, pUdt, "CANCEL_REASON", CANCEL_REASON);
OracleUdt.SetValue(con, pUdt, "SEND_TO_COURT_ROLE", SEND_TO_COURT_ROLE);
OracleUdt.SetValue(con, pUdt, "NOTES", NOTES);
OracleUdt.SetValue(con, pUdt, "APPLICATION_AND_VERSION", APPLICATION_AND_VERSION);
OracleUdt.SetValue(con, pUdt, "ISSUED_DATE", ISSUED_DATE);
OracleUdt.SetValue(con, pUdt, "MACHINE_IDENTIFIER", MACHINE_IDENTIFIER);
OracleUdt.SetValue(con, pUdt, "CAMERA_HWID", CAMERA_HWID);
OracleUdt.SetValue(con, pUdt, "EVENT_ID", EVENT_ID);
OracleUdt.SetValue(con, pUdt, "INFRINGEMENT_LOCATION_CODE", INFRINGEMENT_LOCATION_CODE);
OracleUdt.SetValue(con, pUdt, "EXTERNAL_TOKEN", EXTERNAL_TOKEN);
OracleUdt.SetValue(con, pUdt, "EXTERNAL_REFERENCE", EXTERNAL_REFERENCE);
OracleUdt.SetValue(con, pUdt, "CHARGE_DESCRIPTION_1", CHARGE_DESCRIPTION_1);
OracleUdt.SetValue(con, pUdt, "CHARGE_DESCRIPTION_2", CHARGE_DESCRIPTION_2);
OracleUdt.SetValue(con, pUdt, "CHARGE_DESCRIPTION_3", CHARGE_DESCRIPTION_3);
}
private void ExcecuteNonQuery(Oracle.DataAccess.Client.OracleCommand command, string storedProcName, Oracle.DataAccess.Client.OracleConnection dbConnection)
{
command.Connection = dbConnection;
command.CommandText = storedProcName;
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
TICKET_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "TICKET_NUMBER");
PERSON_INFO_ID = (long?)OracleUdt.GetValue(con, pUdt, "PERSON_INFO_ID");
TITLE = (string)OracleUdt.GetValue(con, pUdt, "TITLE");
FIRST_NAME = (string)OracleUdt.GetValue(con, pUdt, "FIRST_NAME");
MIDDLE_NAMES = (string)OracleUdt.GetValue(con, pUdt, "MIDDLE_NAMES");
SURNAME = (string)OracleUdt.GetValue(con, pUdt, "SURNAME");
INITIALS = (string)OracleUdt.GetValue(con, pUdt, "INITIALS");
IDENTIFICATION_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "IDENTIFICATION_NUMBER");
IDENTIFICATION_TYPE_ID = (long?)OracleUdt.GetValue(con, pUdt, "IDENTIFICATION_TYPE_ID");
IDENTIFICATION_COUNTRY_ID = (long?)OracleUdt.GetValue(con, pUdt, "IDENTIFICATION_COUNTRY_ID");
CITIZEN_TYPE_ID = (long?)OracleUdt.GetValue(con, pUdt, "CITIZEN_TYPE_ID");
GENDER = (string)OracleUdt.GetValue(con, pUdt, "GENDER");
AGE = (int?)OracleUdt.GetValue(con, pUdt, "AGE");
BIRTHDATE = (DateTime?)OracleUdt.GetValue(con, pUdt, "BIRTHDATE");
OCCUPATION = (string)OracleUdt.GetValue(con, pUdt, "OCCUPATION");
TELEPHONE = (string)OracleUdt.GetValue(con, pUdt, "TELEPHONE");
MOBILE_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "MOBILE_NUMBER");
FAX = (string)OracleUdt.GetValue(con, pUdt, "FAX");
EMAIL = (string)OracleUdt.GetValue(con, pUdt, "EMAIL");
COMPANY = (string)OracleUdt.GetValue(con, pUdt, "COMPANY");
BUSINESS_TELEPHONE = (string)OracleUdt.GetValue(con, pUdt, "BUSINESS_TELEPHONE");
PHYSICAL_ADDRESS_INFO_ID = (long?)OracleUdt.GetValue(con, pUdt, "PHYSICAL_ADDRESS_INFO_ID");
PHYSICAL_ADDRESS_TYPE_ID = (long?)OracleUdt.GetValue(con, pUdt, "PHYSICAL_ADDRESS_TYPE_ID");
PHYSICAL_STREET_1 = (string)OracleUdt.GetValue(con, pUdt, "PHYSICAL_STREET_1");
PHYSICAL_STREET_2 = (string)OracleUdt.GetValue(con, pUdt, "PHYSICAL_STREET_2");
PHYSICAL_SUBURB = (string)OracleUdt.GetValue(con, pUdt, "PHYSICAL_SUBURB");
PHYSICAL_TOWN = (string)OracleUdt.GetValue(con, pUdt, "PHYSICAL_TOWN");
PHYSICAL_CODE = (string)OracleUdt.GetValue(con, pUdt, "PHYSICAL_CODE");
POSTAL_ADDRESS_INFO_ID = (long?)OracleUdt.GetValue(con, pUdt, "POSTAL_ADDRESS_INFO_ID");
POSTAL_ADDRESS_TYPE_ID = (long?)OracleUdt.GetValue(con, pUdt, "POSTAL_ADDRESS_TYPE_ID");
POSTAL_PO_BOX = (string)OracleUdt.GetValue(con, pUdt, "POSTAL_PO_BOX");
POSTAL_STREET = (string)OracleUdt.GetValue(con, pUdt, "POSTAL_STREET");
POSTAL_SUBURB = (string)OracleUdt.GetValue(con, pUdt, "POSTAL_SUBURB");
POSTAL_TOWN = (string)OracleUdt.GetValue(con, pUdt, "POSTAL_TOWN");
POSTAL_CODE = (string)OracleUdt.GetValue(con, pUdt, "POSTAL_CODE");
OFFENCE_LOCATION_STREET = (string)OracleUdt.GetValue(con, pUdt, "OFFENCE_LOCATION_STREET");
OFFENCE_LOCATION_SUBURB = (string)OracleUdt.GetValue(con, pUdt, "OFFENCE_LOCATION_SUBURB");
OFFENCE_LOCATION_TOWN = (string)OracleUdt.GetValue(con, pUdt, "OFFENCE_LOCATION_TOWN");
OFFENCE_LOCATION_LATITUDE = (decimal?)OracleUdt.GetValue(con, pUdt, "OFFENCE_LOCATION_LATITUDE");
OFFENCE_LOCATION_LONGITUDE = (decimal?)OracleUdt.GetValue(con, pUdt, "OFFENCE_LOCATION_LONGITUDE");
VEHICLE_REGISTRATION_MAIN = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_REGISTRATION_MAIN");
VEHICLE_REGISTRATION_NO_2 = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_REGISTRATION_NO_2");
VEHICLE_REGISTRATION_NO_3 = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_REGISTRATION_NO_3");
VEHICLE_MAKE_MAIN = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_MAKE_MAIN");
VEHICLE_MODEL_MAIN = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_MODEL_MAIN");
VEHICLE_TYPE_MAIN = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_TYPE_MAIN");
VEHICLE_LICENCE_EXPIRY_DATE = (DateTime?)OracleUdt.GetValue(con, pUdt, "VEHICLE_LICENCE_EXPIRY_DATE");
VEHICLE_COLOUR = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_COLOUR");
VEHICLE_REGISTER_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_REGISTER_NUMBER");
VEHICLE_ENGINE_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_ENGINE_NUMBER");
VEHICLE_CHASSIS_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "VEHICLE_CHASSIS_NUMBER");
GUARDIAN = (string)OracleUdt.GetValue(con, pUdt, "GUARDIAN");
DIRECTION = (string)OracleUdt.GetValue(con, pUdt, "DIRECTION");
METER_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "METER_NUMBER");
CASE_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "CASE_NUMBER");
CC_NUMBER = (string)OracleUdt.GetValue(con, pUdt, "CC_NUMBER");
CHARGE_CODE_1 = (string)OracleUdt.GetValue(con, pUdt, "CHARGE_CODE_1");
CHARGE_CODE_1_ID = (long?)OracleUdt.GetValue(con, pUdt, "CHARGE_CODE_1_ID");
AMOUNT_1 = (decimal?)OracleUdt.GetValue(con, pUdt, "AMOUNT_1");
CHARGE_CODE_2 = (string)OracleUdt.GetValue(con, pUdt, "CHARGE_CODE_2");
CHARGE_CODE_2_ID = (long?)OracleUdt.GetValue(con, pUdt, "CHARGE_CODE_2_ID");
AMOUNT_2 = (decimal?)OracleUdt.GetValue(con, pUdt, "AMOUNT_2");
CHARGE_CODE_3 = (string)OracleUdt.GetValue(con, pUdt, "CHARGE_CODE_3");
CHARGE_CODE_3_ID = (long?)OracleUdt.GetValue(con, pUdt, "CHARGE_CODE_3_ID");
AMOUNT_3 = (decimal?)OracleUdt.GetValue(con, pUdt, "AMOUNT_3");
HAS_ALTERNATIVE_CHARGE = (long?)OracleUdt.GetValue(con, pUdt, "HAS_ALTERNATIVE_CHARGE");
OFFENCE_DATE = (DateTime?)OracleUdt.GetValue(con, pUdt, "OFFENCE_DATE");
COURT_DATE = (DateTime?)OracleUdt.GetValue(con, pUdt, "COURT_DATE");
COURT_NAME = (string)OracleUdt.GetValue(con, pUdt, "COURT_NAME");
COURT_ROOM = (string)OracleUdt.GetValue(con, pUdt, "COURT_ROOM");
DISTRICT_NAME = (string)OracleUdt.GetValue(con, pUdt, "DISTRICT_NAME");
PAYMENT_PLACE = (string)OracleUdt.GetValue(con, pUdt, "PAYMENT_PLACE");
PAYMENT_DATE = (DateTime?)OracleUdt.GetValue(con, pUdt, "PAYMENT_DATE");
OFFICER_CREDENTIAL_ID = (long?)OracleUdt.GetValue(con, pUdt, "OFFICER_CREDENTIAL_ID");
CAPTURED_DATE = (DateTime?)OracleUdt.GetValue(con, pUdt, "CAPTURED_DATE");
CAPTURED_CREDENTIAL_ID = (long?)OracleUdt.GetValue(con, pUdt, "CAPTURED_CREDENTIAL_ID");
LICENCE_CODE = (string)OracleUdt.GetValue(con, pUdt, "LICENCE_CODE");
LICENCE_TYPE = (string)OracleUdt.GetValue(con, pUdt, "LICENCE_TYPE");
DRIVER_LICENCE_CERTIFICATE_NO = (string)OracleUdt.GetValue(con, pUdt, "DRIVER_LICENCE_CERTIFICATE_NO");
MODIFIED_DATE = (DateTime?)OracleUdt.GetValue(con, pUdt, "MODIFIED_DATE");
MODIFIED_CREDENTIAL_ID = (long?)OracleUdt.GetValue(con, pUdt, "MODIFIED_CREDENTIAL_ID");
SPEED = (long?)OracleUdt.GetValue(con, pUdt, "SPEED");
MASS_PERCENTAGE = (decimal?)OracleUdt.GetValue(con, pUdt, "MASS_PERCENTAGE");
IS_CANCELLED = (long?)OracleUdt.GetValue(con, pUdt, "IS_CANCELLED");
CANCEL_REASON = (string)OracleUdt.GetValue(con, pUdt, "CANCEL_REASON");
SEND_TO_COURT_ROLE = (long?)OracleUdt.GetValue(con, pUdt, "SEND_TO_COURT_ROLE");
NOTES = (string)OracleUdt.GetValue(con, pUdt, "NOTES");
APPLICATION_AND_VERSION = (string)OracleUdt.GetValue(con, pUdt, "APPLICATION_AND_VERSION");
ISSUED_DATE = (DateTime?)OracleUdt.GetValue(con, pUdt, "ISSUED_DATE");
MACHINE_IDENTIFIER = (string)OracleUdt.GetValue(con, pUdt, "MACHINE_IDENTIFIER");
CAMERA_HWID = (string)OracleUdt.GetValue(con, pUdt, "CAMERA_HWID");
EVENT_ID = (string)OracleUdt.GetValue(con, pUdt, "EVENT_ID");
INFRINGEMENT_LOCATION_CODE = (string)OracleUdt.GetValue(con, pUdt, "INFRINGEMENT_LOCATION_CODE");
EXTERNAL_TOKEN = (string)OracleUdt.GetValue(con, pUdt, "EXTERNAL_TOKEN");
EXTERNAL_REFERENCE = (string)OracleUdt.GetValue(con, pUdt, "EXTERNAL_REFERENCE");
CHARGE_DESCRIPTION_1 = (string)OracleUdt.GetValue(con, pUdt, "CHARGE_DESCRIPTION_1");
CHARGE_DESCRIPTION_2 = (string)OracleUdt.GetValue(con, pUdt, "CHARGE_DESCRIPTION_2");
CHARGE_DESCRIPTION_3 = (string)OracleUdt.GetValue(con, pUdt, "CHARGE_DESCRIPTION_3");
}
protected void RadAsyncUpload1_FileUploaded(object sender, Telerik.Web.UI.FileUploadedEventArgs e)
{
string SavedDoc = string.Empty;
string fileName = string.Empty;
// try
{
int i = RadAsyncUpload1.InitialFileInputsCount;
try
{
using (Stream str = e.File.InputStream);
}
catch
{
string strError = "<Script>FileTimedOut()</script>";
this.Page.ClientScript.RegisterStartupScript(this.Page.GetType(), "strError", strError);
return;
}
using (Stream str = e.File.InputStream)
{
byte[] content = new byte[str.Length];
content = ReadFully(str);
uploadedFiles.Add(content);
fileName = e.File.FileName.Replace("'", "");
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
try
{
SQLStatic.Sessions.SetSessionValue(session_id, "UPLOAD_MESSAGE", txtmsgUpload.Text);
Data.SavePDF(session_id, fileName, content);
hidUpload.Value = "1";
}
catch
{
}
str.Read(content, 0, content.Length);
ViewState["Docs"] = ViewState["Docs"].ToString() + "<li>" + fileName + "</li>";
}
}
//catch
//{
// ViewState["Error"] = "T";
//}
if (hidcount.Value.Length > 0)
{
hidcount.Value = hidcount.Value.Remove(0, 1);
}
if (hidcount.Value.Length == 0)
{
if (!string.IsNullOrEmpty(ViewState["Docs"].ToString()))
{
SavedDoc = "<br/><u><b>Documents Uploaded Successfully</b></u><ul>" + ViewState["Docs"].ToString() + "</ul>";
}
if (ViewState["Error"].ToString().Equals("F"))
{
ConfromSave(SavedDoc);
}
else
{
if (string.IsNullOrEmpty(ViewState["Docs"].ToString()))
{
SavedDoc = "<br/><br/><u><b>Documents Uploaded Successfully</b></u><ul> None</ul>";
}
else
{
SavedDoc = "<br/><br/><u><b>Documents Uploaded Successfully</b></u><ul>" + ViewState["Docs"].ToString() + "</ul>";
}
RadWindowManager1.RadAlert("Upload Failed. Please Try Again." + SavedDoc, 300, 200, "Upload Failed", null);
}
ViewState["Docs"] = string.Empty;
//btnSubmit.Enabled = false;
}
txtmsgUpload.Text = string.Empty;
rgDoc.Rebind();
string strLE_EE_ID = SQLStatic.Sessions.GetSessionValue(session_id, "LE_EE_ID");
string strGp = "<Script>Go('" + strLE_EE_ID + "')</script>";
this.Page.ClientScript.RegisterStartupScript(this.Page.GetType(), "strGp", strGp);
}
private void GetExistingData()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.GetExpenseList", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", Request.Params["expid"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "expenses_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
foreach (DataRow dr in tbl.Rows)
{
txtExpenseName.Text = dr["expense_type"].ToString();
lblAmount.Text = Convert.ToDouble(dr["amount"].ToString()).ToString("$#,##0.00");
lblApprovedAmount.Text = Convert.ToDouble(dr["approved_amount"].ToString()).ToString("$#,##0.00");
lblPaidAmount.Text = Convert.ToDouble(dr["Paid_amount"].ToString()).ToString("$#,##0.00");
if (Request.Params["w"] == "s")
{
txtApprovedAmount.Text = dr["approved_amount"].ToString();
txtApprovedAmount.Visible = true;
lblApprovedAmount.Visible = false;
}
else
{
txtPaidAmount.Text = dr["approved_amount"].ToString();
txtPaidAmount.Visible = true;
lblPaidAmount.Visible = false;
}
txtDescription.Text = dr["note"].ToString();
ListItem li0 = new ListItem("--Select--", " ");
ddlExpenseType.Items.Add(li0);
if (dr["expense_type_ID"].ToString() != "1000")
{
ListItem li = new ListItem(txtExpenseName.Text, dr["expense_type_ID"].ToString());
ddlExpenseType.Items.Add(li);
}
for (int i = 0; i < ddlExpenseType.Items.Count; i++)
{
if (ddlExpenseType.Items[i].Value == dr["expense_type_ID"].ToString())
{
ddlExpenseType.SelectedIndex = i;
break;
}
}
txtVedorName.Text = dr["vendor_name"].ToString();
txtVendorContact.Text = dr["vendor_contact_name"].ToString();
txtAddressLine1.Text = dr["vendor_address_1"].ToString();
txtAddressLine2.Text = dr["vendor_address2"].ToString();
txtCity.Text = dr["vendor_city"].ToString();
string strState = dr["vendor_state"].ToString();
txtZipCode.Text = dr["vendor_zip"].ToString();
txtPhoneNumber.Text = dr["vendor_phone"].ToString();
txtFaxNumber.Text = dr["vendor_fax"].ToString();
txtemail.Text = dr["vendor_email"].ToString();
txtProvince.Text = dr["province"].ToString();
string strCountry = dr["country_code"].ToString();
foreach (ListItem li in ddlContries.Items)
{
li.Selected = li.Value == strCountry;
}
for (int i = 0; i < ddlStates.Items.Count; i++)
{
if (ddlStates.Items[i].Value == strState)
{
ddlStates.SelectedIndex = i;
break;
}
}
ddlExpenseType_SelectedIndexChanged(null, null);
txtExpenseName.Text = dr["expense_type"].ToString();
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
tbl.Dispose();
}
setCountryFields();
}
private void FillDataTypesNeeds()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.TrainingTypesNeedsRecordDesc", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Step_4_record", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
if (tbl.Rows.Count > 0)
{
SetValue(txtSourseTraining, tbl.Rows[0]["source_of_training"].ToString());
SetValue(txtPurposeOfTraining, tbl.Rows[0]["purpose_of_training"].ToString());
SetValue(txtProgramCode, tbl.Rows[0]["program_code_description"].ToString());
SetValue(txtAccountNumber, tbl.Rows[0]["account_no"].ToString());
SetValue(txtDepartmentID, tbl.Rows[0]["dept_id"].ToString());
SetValue(txtAccomodation, tbl.Rows[0]["ee_need_accomodation"].ToString());
SetValue(txtAccomodationDescription, tbl.Rows[0]["accomodation_description"].ToString());
SetValue(txtAccomodationDescription, tbl.Rows[0]["accomodation_description"].ToString());
SetValue(txtPositionLevel, tbl.Rows[0]["position_level"].ToString());
SetValue(txTypeofAppointment, tbl.Rows[0]["type_of_appointment"].ToString());
SetValue(txtEducationLevel, tbl.Rows[0]["frm_education_level"].ToString());
SetValue(txtTrainingTypeCode, tbl.Rows[0]["frm_training_type_code"].ToString());
SetValue(txtTrainingSubTypeCode, tbl.Rows[0]["frm_training_sub_type_code"].ToString());
SetValue(txtDelivaryTypeCode, tbl.Rows[0]["training_delivery_type_code"].ToString());
SetValue(txtDesignationTypeCode, tbl.Rows[0]["training_designation_code"].ToString());
SetValue(txtCreditTypeCode, tbl.Rows[0]["training_credit_type_code"].ToString());
SetValue(txtAccredetionIndicator, tbl.Rows[0]["accredetion_indicator"].ToString());
SetValue(txtTrainingCredit, tbl.Rows[0]["training_credit"].ToString());
SetValue(txtTypeofDevelopmentOther182, tbl.Rows[0]["Type_Of_Development_Other"].ToString());
SetValue(txtTypeOfDevelopmentSummary, tbl.Rows[0]["TypeofDevelopment"].ToString());
if (txtTypeOfDevelopmentSummary.Text.Length > 2)
{
txtTypeOfDevelopmentSummary.Text = txtTypeOfDevelopmentSummary.Text.Substring(2);
}
txtAccomodationDescription.Visible = (txtAccomodation.Text == "Yes");
lblAccomodationDescription.Visible = txtAccomodationDescription.Visible;
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
{
tbl.Dispose();
}
}
}
public static void SaveRate_ver2_tbl(string account_number_, string version_number_, string class_code_, string category_code_, string category_plan_,
string family_status_, string processing_year_, string batch_, string rates_, string age_selected_code_, string TobacoRate_, string user_name_,
string long_description_, string age_rate_desc_, string column_id_, string ratetype_, Oracle.DataAccess.Client.OracleConnection conn)
{
ArrayList ar = new ArrayList(12);
ar.Add(SQLStatic.StoredProcedure.OneParamerer("account_number_", "in", "varchar2", account_number_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("version_number_", "in", "varchar2", version_number_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("calss_code_", "in", "varchar2", class_code_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("category_code_", "in", "varchar2", category_code_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("category_plan_", "in", "varchar2", category_plan_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("family_status_", "in", "varchar2", family_status_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("processing_year_", "in", "varchar2", processing_year_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("rates_", "in", "varchar2", rates_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("age_selected_code_", "in", "varchar2", age_selected_code_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("TobacoRate_", "in", "varchar2", TobacoRate_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("long_description_", "in", "varchar2", long_description_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("age_rate_desc_", "in", "varchar2", age_rate_desc_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("user_name_", "in", "varchar2", user_name_));
ar.Add(SQLStatic.StoredProcedure.OneParamerer("column_id_", "in", "varchar2", column_id_));
SQLStatic.StoredProcedure.ExecuteNonQueryProcedure("pkg_automated_rate_update.SaveRate_ver2_tbl", ar, conn);
}
protected void Page_Load(object sender, EventArgs e)
{
session_id = Request.Cookies["Session_ID"].Value.ToString();
#region BasTemplate
if (!IsPostBack)
{
Template.BasTemplate objBasTemplate = new Template.BasTemplate();
try
{
if (Request.Cookies["Session_ID"] == null)
{
Response.Redirect("/web_projects/login_error/ErrorPage.aspx?error=Cookie not found. Please signon first", true);
}
string strResult = objBasTemplate.Authenticate(Request.Cookies["Session_ID"].Value.ToString(), Request.Url.Authority.ToString(), Request.Url.AbsolutePath.ToString(), true, false);
if (strResult != "")
{
Response.Redirect("/web_projects/login_error/ErrorPage.aspx?error=" + strResult, false);
return;
}
objBasTemplate.SetSeatchField(0);
objBasTemplate.ShowFontSizeSelector = false;
objBasTemplate.ShowNotes = false;
objBasTemplate.ShowProcessingYear = true;
ViewState["AccessType"] = objBasTemplate.strAccessType;
ViewState["Employee_Number"] = objBasTemplate.strEmployee_Number;
ViewState["Processing_Year"] = objBasTemplate.strProcessingYear;
ViewState["Role_Restriction_Level"] = objBasTemplate.strRole_Restriction_Level;
ViewState["Selected_Account_Number"] = objBasTemplate.strSelected_Account_Number;
ViewState["Selected_Employee_Class_Code"] = objBasTemplate.strSelected_Employee_Class_Code;
ViewState["User_Group_ID"] = objBasTemplate.strUser_Group_ID;
ViewState["User_ID"] = objBasTemplate.strUser_ID;
ViewState["User_Name"] = objBasTemplate.strUser_Name;
ViewState["User_Primary_Role"] = objBasTemplate.strUser_Primary_Role;
}
catch (Exception ex)
{
string strError = "Error Message: " + ex.Message + "~~Application:" + ex.Source + "~~Method:" + ex.TargetSite + "~~Detail:" + ex.StackTrace;
Response.Redirect("/web_projects/login_error/ErrorPage.aspx?error=" + strError.Replace("\n", "~"));
}
finally
{
objBasTemplate.CleanUp();
objBasTemplate.Dispose();
}
}
#endregion
lblScript.Text = string.Empty;
if (!IsPostBack)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
try
{
ViewState["Account_number"] = SQLStatic.Sessions.GetSessionValue(session_id, "ACCOUNT_NUMBER", conn);
ViewState["Version"] = SQLStatic.Sessions.GetSessionValue(session_id, "Version", conn);
ViewState["class_code"] = SQLStatic.Sessions.GetSessionValue(session_id, "class_code", conn);
ViewState["category_code"] = SQLStatic.Sessions.GetSessionValue(session_id, "category_code", conn);
ViewState["category_plan"] = SQLStatic.Sessions.GetSessionValue(session_id, "category_plan", conn);
ViewState["PROCESSING_YEAR"] = SQLStatic.Sessions.GetSessionValue(session_id, "PROCESSING_YEAR", conn);
ViewState["CVRG"] = SQLStatic.Sessions.GetSessionValue(session_id, "CVRG", conn);
ViewState["CVRGID"] = SQLStatic.Sessions.GetSessionValue(session_id, "CVRGID", conn);
}
finally
{
SQLStatic.SQL.CloseConnection(conn);
}
}
btnRestRates.Attributes.Add("onclick", "CheckRestRate()");
if (!string.IsNullOrEmpty(hidReset.Value))
{
hidSave.Value = string.Empty;
RestRate();
Response.Redirect("/WEB_PROJECTS/Rates/Default.aspx", true);
return;
}
}
public static void UpdateRates_One_Cvrg(string account_number, string cvrg_rec_, string strBatch, Oracle.DataAccess.Client.OracleConnection conn)
{
ArrayList al = new ArrayList(7);
al.Add(SQLStatic.StoredProcedure.OneParamerer("cvrg_rec_", "in", "varchar2", cvrg_rec_));
al.Add(SQLStatic.StoredProcedure.OneParamerer("account_number_", "in", "varchar2", account_number));
al.Add(SQLStatic.StoredProcedure.OneParamerer("batch_id_", "in", "varchar2", strBatch));
SQLStatic.StoredProcedure.ExecuteNonQueryProcedure("pkg_automated_rate_update.UpdateRates_One_Cvrg", al, conn);
}
private bool DoSave()
{
if (ddlExpenseType.SelectedValue != "1000")
{
txtExpenseName.Text = ddlExpenseType.SelectedItem.Text;
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
string strProcedureName = "basdba.PKG_Training.AddUpdateEmploeeExpense";
if (Request.Params["Admin"] == "YES")
{
strProcedureName = "basdba.PKG_Training.AddUpdateEmploeeExpense_Admin";
}
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
bool blnok = false;
conn.Open();
Oracle.DataAccess.Client.OracleTransaction txn = conn.BeginTransaction();
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", Request.Params["expid"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "expense_type_ID_", "number", "in", ddlExpenseType.SelectedValue);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "expense_type_", "varchar2", "in", txtExpenseName.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "amount_", "number", "in", txtAmount.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "pay_to_", "number", "in", optlstVendor.SelectedIndex);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "note_", "varchar2", "in", txtDescription.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_name_", "varchar2", "in", txtVedorName.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_contact_name_", "varchar2", "in", txtVendorContact.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_address_1_", "varchar2", "in", txtAddressLine1.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_address2_", "varchar2", "in", txtAddressLine2.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_city_", "varchar2", "in", txtCity.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_state_", "varchar2", "in", ddlStates.SelectedValue);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_zip_", "varchar2", "in", txtZipCode.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_phone_", "varchar2", "in", txtPhoneNumber.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_fax_", "varchar2", "in", txtFaxNumber.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_emai_", "varchar2", "in", txtemail.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "country_code_", "varchar2", "in", ddlContries.SelectedValue);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "province_", "varchar2", "in", txtProvince.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "new_record_id_", "number", "out", "");
cmd.ExecuteNonQuery();
double dblremain = remaining_amount(conn);
if (dblremain < 0)
{
txn.Rollback();
blnok = false;
lblError.Text = "You do not have enough money in your budget to pay for this expense. You " +
"should request at lease " + (-1 * dblremain).ToString("$###,###,##0.00") + " extra from the Division/Office Director to pay for this expense";
}
else
{
blnok = true;
txn.Commit();
}
}
catch
{
txn.Rollback();
throw;
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
return(blnok);
}
public static void add_formulas_for_COBRA_Setup(string account_number, string coverage_id, Oracle.DataAccess.Client.OracleConnection conn)
{
ArrayList al = new ArrayList(2);
al.Add(SQLStatic.StoredProcedure.OneParamerer("account_number_", "in", "varchar2", account_number));
al.Add(SQLStatic.StoredProcedure.OneParamerer("user_id_", "in", "varchar2", coverage_id));
SQLStatic.StoredProcedure.ExecuteNonQueryProcedure("pkg_automated_rate_update.add_formulas_for_COBRA_Setup", al, conn);
}
public static string AvailableAmount(string employee_number, string processing_year, Oracle.DataAccess.Client.OracleConnection conn)
{
string strAmount = SQLStatic.SQL.ExecScaler("select pkg_training.availablebalance(" + employee_number + "," + processing_year + ") from dual",
conn).ToString();
if (strAmount == "")
{
return("$0.00");
}
else
{
return(Convert.ToDouble(strAmount).ToString("$##,0.00"));
}
}
public static string SaveIdentification(string session_id, string FirstName, string MidInitial, string LastName, string DateofBirth, string Gender,
string MaritalStatus, string HireDate, string Salary, string JobTitle, string Salutation, string SSN, string depCode, string location,
string clien_number, string EE_exposed_company_id, string Nickname_, string Workers_Comp_Class_, Oracle.DataAccess.Client.OracleConnection conn)
{
ArrayList al = new ArrayList(18);
al.Add(SQLStatic.StoredProcedure.OneParamerer("session_id_", "in", "varchar2", session_id));
al.Add(SQLStatic.StoredProcedure.OneParamerer("FirstName_", "in", "varchar2", FirstName));
al.Add(SQLStatic.StoredProcedure.OneParamerer("MidInitial_", "in", "varchar2", MidInitial));
al.Add(SQLStatic.StoredProcedure.OneParamerer("LastName_", "in", "varchar2", LastName));
al.Add(SQLStatic.StoredProcedure.OneParamerer("DateofBirth_", "in", "date", DateofBirth));
al.Add(SQLStatic.StoredProcedure.OneParamerer("Gender_", "in", "varchar2", Gender));
al.Add(SQLStatic.StoredProcedure.OneParamerer("MaritalStatus_", "in", "varchar2", MaritalStatus));
al.Add(SQLStatic.StoredProcedure.OneParamerer("HireDate_", "in", "date", HireDate));
al.Add(SQLStatic.StoredProcedure.OneParamerer("Salary_", "in", "number", Salary));
al.Add(SQLStatic.StoredProcedure.OneParamerer("JobTitle_", "in", "varchar2", JobTitle));
al.Add(SQLStatic.StoredProcedure.OneParamerer("Salutation_", "in", "varchar2", Salutation));
al.Add(SQLStatic.StoredProcedure.OneParamerer("SSN_", "in", "varchar2", SSN));
al.Add(SQLStatic.StoredProcedure.OneParamerer("depCode_", "in", "varchar2", depCode));
al.Add(SQLStatic.StoredProcedure.OneParamerer("location_", "in", "varchar2", location));
al.Add(SQLStatic.StoredProcedure.OneParamerer("clien_number_", "in", "varchar2", clien_number));
al.Add(SQLStatic.StoredProcedure.OneParamerer("Nickname_", "in", "varchar2", Nickname_));
al.Add(SQLStatic.StoredProcedure.OneParamerer("Workers_Comp_Class_", "in", "varchar2", Workers_Comp_Class_));
al.Add(SQLStatic.StoredProcedure.OneParamerer("employee_number_", "out", "number", null));
ArrayList alRet = SQLStatic.StoredProcedure.ExecuteNonQueryProcedure("pkg_newhirewizard2.SaveIdentification", al, conn);
string strReturn = alRet[0].ToString();
return(strReturn.ToLower().Replace("employee_number_=", ""));
}
private void DrawGrig()
{
if (ViewState["typeof"].ToString().Equals("0"))
{
DataTable tbl = Data.OptionalBenefits(session_id);
foreach (DataRow dr in tbl.Rows)
{
if (dr["Category_PlanCode"].ToString().Equals("001LIF-CYL"))
{
decimal bl = Convert.ToDecimal(dr["multiplier_override"]);
dr["BenefitLevel"] = String.Format("{0:C}", bl);
}
}
foreach (DataRow dr in tbl.Rows)
{
if (!string.IsNullOrEmpty(dr["End Date"].ToString()))
{
DateTime dtermdate = Convert.ToDateTime(dr["End Date"].ToString());
if (DateTime.Today > dtermdate)
{
dr.Delete();
}
}
}
foreach (DataRow dr in tbl.Rows)
{
DateTime dtermdate = Convert.ToDateTime(dr["effective_date"].ToString());
dr["effective_date"] = dtermdate.ToString("MM/dd/yy");
dtermdate = Convert.ToDateTime(dr["Last Change Date"].ToString());
dr["Last Change Date"] = dtermdate.ToString("MM/dd/yy");
}
tbl.AcceptChanges();
//int col_number = ColumnNumber();
//lblCostBase.Visible = true;
//if (!col_number.Equals(-1))
//{
// gvSummary.Columns[col_number].Visible = true;
// string strheader = Data.PerpayText(ViewState["Selected_Account_Number"].ToString());
// if (!string.IsNullOrEmpty(strheader))
// gvSummary.Columns[col_number].HeaderText = strheader;
//}
//else
// lblCostBase.Visible = false;
gvSummary.DataSource = tbl;
gvSummary.DataBind();
}
DataTable tblDep = Data.GetDependentsListActive(ViewState["Employee_Number"].ToString());
gvDep.DataSource = tblDep;
gvDep.DataBind();
if (tblDep.Rows.Count.Equals(0))
{
lblNoDependent.Visible = true;
}
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
DataTable tblPend = Data.GetBenefitStatmentPendingList(session_id, conn);
if (!tblPend.Rows.Count.Equals(0))
{
gvEOI.DataSource = tblPend;
gvEOI.DataBind();
}
else
{
gvEOI.Visible = false;
lblNoPendingElection.Visible = true;
divPendingInstruction.Visible = false;
}
tx.Rollback();
SQLStatic.SQL.CloseConnection(conn);
if (Data.benefitpagevisible(ViewState["Selected_Account_Number"].ToString()))
{
Label2.Visible = true;
DataTable tblBenef = Data.GetBeneficiary(session_id);
if (!tblBenef.Rows.Count.Equals(0))
{
gvBenefit.DataSource = tblBenef;
gvBenefit.DataBind();
}
else
{
gvBenefit.Visible = false;
lblNoBeneficireies.Visible = true;
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
try
{
string strInOPenEE = "0";
string newCatePlN = Request.Params["catplan"];
if (cbOe.Checked)
{
strInOPenEE = "1";
}
Data.Save_In_OE(Request.Params["accnt"], Request.Params["py"], strInOPenEE, Request.Params["catcode"],
Request.Params["catplan"], Request.Params["class"], Request.Params["batch"], conn);
if (!Request.Params["action"].Equals("0"))
{
newCatePlN = Data.Replace_coverage(Request.Params["accnt"], Request.Params["ver"], Request.Params["class"], Request.Params["catcode"],
Request.Params["catplan"], Request.Params["py"], Request.Params["batch"], Request.Params["ratetype"], conn);
}
if (string.IsNullOrEmpty(hidcomVales.Value))
{
tx.Commit();
SQLStatic.SQL.CloseConnection(conn);
if (!Request.Params["action"].Equals("0"))
{
jscriptsFunctions.Misc.Alert(Page, "You did not enter Rates");
btnCancel_Click(null, null);
return;
}
else
{
btnCancel_Click(null, null);
}
}
if (!string.IsNullOrEmpty(hidcomVales.Value))
{
string long_description = Request.Params["cvrg"];
if (!Request.Params["cvrg"].IndexOf(" - ").Equals(-1))
{
long_description = Request.Params["cvrg"].Substring(0, Request.Params["cvrg"].IndexOf(" - "));
}
hidcomVales.Value = hidcomVales.Value.Remove(0, 1);
ArrayList ar = new ArrayList(hidcomVales.Value.Split(new char[] { '!' }));
foreach (string str in ar)
{
string[] fs = str.Split('=');
Data.SaveRate_ver1(Request.Params["accnt"], Request.Params["ver"], Request.Params["class"], Request.Params["catcode"],
newCatePlN, fs[0], Request.Params["py"], Request.Params["batch"], fs[1], "0", "", "", long_description, "", "1", conn);
}
}
tx.Commit();
}
catch
{
tx.Rollback();
throw;
}
finally
{
SQLStatic.SQL.CloseConnection(conn);
}
btnCancel_Click(null, null);
}
public static void ProcessPendCoverages(string session_id, string cvrg_group_id, string status, Oracle.DataAccess.Client.OracleConnection conn)
{
ArrayList al = new ArrayList(4);
al.Add(SQLStatic.StoredProcedure.OneParamerer("session_id_", "in", "varchar2", session_id));
al.Add(SQLStatic.StoredProcedure.OneParamerer("cvrg_group_id_", "in", "varchar2", cvrg_group_id));
al.Add(SQLStatic.StoredProcedure.OneParamerer("status_", "in", "varchar2", status));
SQLStatic.StoredProcedure.ExecuteNonQueryProcedure("pkg_enrollment_approvals.ProcessPendCoverages", al, conn);
}
private void FillData()
{
if (ViewState["Request_Record_ID"].ToString()=="-1")
{
txtCourseCode.Text = "";
txtCourseTitle.Text = "";
txtLocation.Text = "";
txtVedorName.Text = "";
txtPhoneNumber.Text = "";
txtFaxNumber.Text = "";
txtAddressLine1.Text = "";
txtAddressLine2.Text = "";
txtCity.Text = "";
ddlStates.SelectedIndex = 0;
txtZipCode.Text = "";
txtWebSite.Text = "";
lblDescribtion.Text = "";
return;
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.GetHeaderRecord",conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_",ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record","cursor","out","");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl=null;
try
{
conn.Open();
da.Fill(mds);
tbl=mds.Tables[0];
txtCourseCode.Text = tbl.Rows[0]["course_code"].ToString();
txtCourseTitle.Text = tbl.Rows[0]["course_title"].ToString();
txtLocation.Text = tbl.Rows[0]["location_of_training"].ToString();
txtVedorName.Text = tbl.Rows[0]["vendor_name"].ToString();
txtVendorContact.Text = tbl.Rows[0]["vendor_contact"].ToString();
txtPhoneNumber.Text = tbl.Rows[0]["vendor_phone_number"].ToString();
txtFaxNumber.Text = tbl.Rows[0]["vendor_fax_number"].ToString();
txtAddressLine1.Text = tbl.Rows[0]["vendor_address1"].ToString();
txtAddressLine2.Text = tbl.Rows[0]["vendor_address2"].ToString();
txtCity.Text = tbl.Rows[0]["vendor_city"].ToString();
string strState = tbl.Rows[0]["vendor_state"].ToString();
txtZipCode.Text = tbl.Rows[0]["vendor_zip_code"].ToString();
txtWebSite.Text = tbl.Rows[0]["vendor_website"].ToString();
if (tbl.Rows[0]["desription_of_course_value"].ToString().Length >100)
lblDescribtion.Text = tbl.Rows[0]["desription_of_course_value"].ToString().Substring(0,95)+"...";
else
lblDescribtion.Text = tbl.Rows[0]["desription_of_course_value"].ToString();
ViewState["application_status"]= tbl.Rows[0]["application_status"].ToString();
ViewState["description"]=tbl.Rows[0]["description"].ToString();
ViewState["Application_Processing_Year"]=tbl.Rows[0]["processing_year"].ToString();
for (int i=0;i<ddlStates.Items.Count;i++)
if (ddlStates.Items[i].Value==strState)
{
ddlStates.SelectedIndex = i;
break;
}
txtState.Text=ddlStates.SelectedItem.Text;
txtTrainingType.Text = tbl.Rows[0]["training_type"].ToString()=="1" ? "Within Employee’s Occupation" : "Outside Employee’s Occupation";
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
tbl.Dispose();
}
if (txtCourseCode.Text!="")
lblCourseTitle.Text= txtCourseCode.Text+" - "+txtCourseTitle.Text;
else
lblCourseTitle.Text=txtCourseTitle.Text;
}
public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
{
ParameterName = (string)OracleUdt.GetValue(con, pUdt, "PARAMETERNAME");
ParameterValue = (string)OracleUdt.GetValue(con, pUdt, "PARAMETERVALUE");
}
/// <summary>手持登录</summary>
public AscmUserInfo MobileLogin(string userId, string userPwd, string connString, ref string errorMsg)
{
AscmUserInfo ascmUserInfo = null;
errorMsg = string.Empty;
using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connString))
{
if (conn.State != System.Data.ConnectionState.Open)
conn.Open();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT userId,userName,password,employeeId,extExpandType,extExpandId FROM ynUser WHERE extExpandId = :extExpandId";
cmd.CommandType = System.Data.CommandType.Text;
Oracle.DataAccess.Client.OracleParameter parm = new Oracle.DataAccess.Client.OracleParameter();
parm.ParameterName = ":extExpandId";
parm.OracleDbType = Oracle.DataAccess.Client.OracleDbType.NVarchar2;
parm.Size = 20;
parm.Value = userId;
parm.Direction = System.Data.ParameterDirection.Input;
cmd.Parameters.Add(parm);
using (Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
cmd.Parameters.Clear();
if (reader.Read())
{
ascmUserInfo = new AscmUserInfo();
ascmUserInfo.userId = reader["userId"].ToString();
ascmUserInfo.userName = reader["userName"].ToString();
ascmUserInfo.password = reader["password"].ToString();
int employeeId = 0;
int.TryParse(reader["employeeId"].ToString(), out employeeId);
ascmUserInfo.employeeId = employeeId;
ascmUserInfo.extExpandType = reader["extExpandType"].ToString();
ascmUserInfo.extExpandId = reader["extExpandId"].ToString();
if (ascmUserInfo.extExpandType == "erp")
{
byte[] result = Encoding.Default.GetBytes(userPwd);
System.Security.Cryptography.MD5 md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
userPwd = BitConverter.ToString(md5.ComputeHash(result)).Replace("-", "");
if (ascmUserInfo.password != userPwd)
{
errorMsg = "密码不正确";
}
else if (!string.IsNullOrEmpty(ascmUserInfo.userName))
{
Oracle.DataAccess.Client.OracleCommand cmd2 = new Oracle.DataAccess.Client.OracleCommand();
cmd2.Connection = conn;
cmd2.CommandText = "SELECT id,name FROM ascm_supplier WHERE docNumber = :docNumber";
cmd2.CommandType = System.Data.CommandType.Text;
cmd2.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter {
ParameterName = ":docNumber",
OracleDbType = Oracle.DataAccess.Client.OracleDbType.NVarchar2,
Size = 20,
Value = ascmUserInfo.userName,
Direction = System.Data.ParameterDirection.Input
});
using (Oracle.DataAccess.Client.OracleDataReader reader2 = cmd2.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
cmd2.Parameters.Clear();
if (reader2.Read())
{
int id = 0;
if (int.TryParse(reader2["id"].ToString(), out id))
{
AscmSupplier ascmSupplier = new AscmSupplier();
ascmSupplier.id = id;
ascmSupplier.name = reader2["name"].ToString();
ascmUserInfo.ascmSupplier = ascmSupplier;
}
}
}
}
}
}
}
}
return ascmUserInfo;
}
