public string[] GetUserDetails(string username, string category, string type, string operid, string searchId) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection conObj = new OracleConnection(ConStr); try { string str = ""; str += " SELECT a.userid, a.username, a.userowner, a.curentcreditlimit, "; str += " a.mobileno, a.useroperid, a.operid, a.opercategory, a.parentid, "; str += " a.distid "; str += " FROM view_hway_userdetails a "; if (type == "0") { str += " where upper(a.username) like upper('" + searchId.ToString() + "%')"; } else if (type == "1") { str += " where upper(a.userowner) like upper('" + searchId.ToString() + "%')"; } if (category == "2") { str += " and a.parentid='" + operid.ToString() + "' "; } else if (category == "5") { str += " and a.distid='" + operid.ToString() + "' "; } else if (category == "3") { str += " and a.operid ='" + operid.ToString() + "'"; } OracleCommand cmd = new OracleCommand(str, conObj); conObj.Open(); List <string> Operators = new List <string>(); OracleDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Operators.Add(dr["username"].ToString()); Operators.Add(dr["userowner"].ToString()); Operators.Add(dr["mobileno"].ToString()); Operators.Add(dr["curentcreditlimit"].ToString()); } string[] prefixTextArray = Operators.ToArray <string>(); conObj.Close(); return(prefixTextArray); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_TransHwayLcoPayment.cs-GetLcoDetails"); return(null); } finally { conObj.Close(); conObj.Dispose(); } }
public string UserLimitRevarsal(Hashtable ht) { OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim()); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_usercrlimit_new", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 100); Cmd.Parameters["in_username"].Value = ht["UserName"]; Cmd.Parameters.Add("in_lcoid", OracleType.Number); Cmd.Parameters["in_lcoid"].Value = ht["LcoId"]; Cmd.Parameters.Add("in_userid", OracleType.VarChar, 100); Cmd.Parameters["in_userid"].Value = ht["UserId"]; Cmd.Parameters.Add("in_amount", OracleType.Number); Cmd.Parameters["in_amount"].Value = ht["Amount"]; Cmd.Parameters.Add("in_flag", OracleType.VarChar, 100); Cmd.Parameters["in_flag"].Value = ht["Flag"]; Cmd.Parameters.Add("in_remark", OracleType.VarChar, 100); Cmd.Parameters["in_remark"].Value = ht["Remark"]; Cmd.Parameters.Add("in_IP", OracleType.VarChar, 100); Cmd.Parameters["in_IP"].Value = ht["IP"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 250); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value); string Str; if (exeResult == 9999) { //Str = "USER credit limit assigning successfully..."; Str = Convert.ToString(Cmd.Parameters["out_data"].Value); } else { //Str = "USER credit limit assigning successfully..."; Str = Convert.ToString(Cmd.Parameters["out_data"].Value); } ConObj.Dispose(); return(Str); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(ht["user"].ToString(), ex.Message.ToString(), "Cls_Data_TransHwayUserCreditLimit.cs"); return("ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public DataSet GetLcoUserDetails(string username, string catid, string operid) { try { DataTable dtLco = new DataTable("Lco"); Cls_Helper objHelper = new Cls_Helper(); string StrLco = ""; string StrUser = ""; if (catid == "2") { StrLco += "select a.num_oper_id,a.var_oper_opername from aoup_operator_Def a , aoup_lcopre_lco_det b "; StrLco += " where a.num_oper_parentid='" + operid + "'"; StrLco += " and b.num_lcomst_operid=a.num_oper_id "; StrLco += " and a.num_oper_category=3"; StrLco += " and a.var_oper_compcode='HWP' "; StrLco += " order by a.var_oper_opername asc "; } else if (catid == "5") { StrLco = "select a.num_oper_id,a.var_oper_opername from aoup_operator_Def a,aoup_lcopre_lco_det b "; StrLco += " where a.num_oper_distid='" + operid + "'"; StrLco += " and b.num_lcomst_operid=a.num_oper_id "; StrLco += " and a.num_oper_category=3"; StrLco += " and a.var_oper_compcode='HWP' "; StrLco += " order by a.var_oper_opername asc "; } else if (catid == "3") { StrLco = "select a.num_oper_id,a.var_oper_opername from aoup_operator_Def a,aoup_lcopre_lco_det b "; StrLco += " where a.num_oper_id='" + operid + "'"; StrLco += " and b.num_lcomst_operid=a.num_oper_id "; StrLco += " and a.num_oper_category=3"; StrLco += " and a.var_oper_compcode='HWP' "; StrLco += " order by a.var_oper_opername asc "; } else if (catid == "10") { StrLco = "select a.num_oper_id,a.var_oper_opername from aoup_operator_Def a,aoup_lcopre_lco_det b,aoup_operator_Def ho "; StrLco += " where ho.num_oper_parentid='" + operid + "'"; StrLco += " and b.num_lcomst_operid=a.num_oper_id "; StrLco += " and a.num_oper_parentid=ho.num_oper_id "; StrLco += " and a.num_oper_category=3"; StrLco += " and a.var_oper_compcode='HWP' "; StrLco += " order by a.var_oper_opername asc "; } else { StrLco += "select a.num_oper_id,a.var_oper_opername from aoup_operator_Def,aoup_lcopre_lco_det b "; StrLco += " where a.num_oper_category=3"; StrLco += " and b.num_lcomst_operid=a.num_oper_id "; StrLco += " and a.var_oper_compcode='HWP' "; StrLco += " order by a.var_oper_opername asc "; } dtLco = objHelper.GetDataTable(StrLco); DataSet dsCompData = new DataSet(); dsCompData.Tables.Add(dtLco); return(dsCompData); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_RptLastFive.cs-GetLcoUserDetails"); return(null); } }
public string UserAccountMap(string username, string IPAdd, string LcoCode, string AccountNo, string ActiveFlag, string Type) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection ConObj = new OracleConnection(ConStr); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("AOUP_LCOPRE_USER_ACC_MAP_INST", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("IN_UserName", OracleType.VarChar, 50); Cmd.Parameters["IN_UserName"].Value = username; Cmd.Parameters.Add("IN_IP", OracleType.VarChar, 50); Cmd.Parameters["IN_IP"].Value = IPAdd; if (LcoCode != "" && LcoCode != null) { Cmd.Parameters.Add("in_LcoCode", OracleType.VarChar, 100); Cmd.Parameters["in_LcoCode"].Value = LcoCode; } else { Cmd.Parameters.Add("in_LcoCode", OracleType.VarChar, 100); Cmd.Parameters["in_LcoCode"].Value = DBNull.Value; } if (AccountNo != "" && AccountNo != null) { Cmd.Parameters.Add("in_AccountNo", OracleType.VarChar, 100); Cmd.Parameters["in_AccountNo"].Value = AccountNo; } else { Cmd.Parameters.Add("in_AccountNo", OracleType.VarChar, 100); Cmd.Parameters["in_AccountNo"].Value = DBNull.Value; } if (ActiveFlag != "" && ActiveFlag != null) { Cmd.Parameters.Add("in_ActiveFlag", OracleType.VarChar, 100); Cmd.Parameters["in_ActiveFlag"].Value = ActiveFlag; } else { Cmd.Parameters.Add("in_ActiveFlag", OracleType.VarChar, 100); Cmd.Parameters["in_ActiveFlag"].Value = DBNull.Value; } if (Type != "" && Type != null) { Cmd.Parameters.Add("in_Type", OracleType.VarChar, 100); Cmd.Parameters["in_Type"].Value = Type; } else { Cmd.Parameters.Add("in_Type", OracleType.VarChar, 100); Cmd.Parameters["in_Type"].Value = DBNull.Value; } Cmd.Parameters.Add("OUT_ERRTEXT", OracleType.LongVarChar, 400000); Cmd.Parameters["OUT_ERRTEXT"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("OUT_ERRCODE", OracleType.Number); Cmd.Parameters["OUT_ERRCODE"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); string exeData = Convert.ToString(Cmd.Parameters["OUT_ERRTEXT"].Value); string exeCode = Convert.ToString(Cmd.Parameters["OUT_ERRCODE"].Value); return(exeCode + "#" + exeData); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_UserAccountMap-UserAccountMap"); return("-300#" + ex.Message.ToString()); } finally { ConObj.Close(); ConObj.Dispose(); } }
public string validateBulkTrans(Hashtable htTransData) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection ConObj = new OracleConnection(ConStr); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_bulk_provi_valid", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 50); Cmd.Parameters["in_username"].Value = htTransData["username"]; Cmd.Parameters.Add("in_lcoid", OracleType.VarChar, 100); Cmd.Parameters["in_lcoid"].Value = htTransData["lcoid"]; Cmd.Parameters.Add("in_lcocode", OracleType.VarChar, 100); Cmd.Parameters["in_lcocode"].Value = htTransData["lco_code"]; Cmd.Parameters.Add("in_custid", OracleType.VarChar, 100); Cmd.Parameters["in_custid"].Value = htTransData["custid"]; Cmd.Parameters.Add("in_vcid", OracleType.VarChar, 100); Cmd.Parameters["in_vcid"].Value = htTransData["vcid"]; Cmd.Parameters.Add("in_custname", OracleType.VarChar, 1000); Cmd.Parameters["in_custname"].Value = htTransData["custname"]; Cmd.Parameters.Add("in_planid", OracleType.VarChar, 100); Cmd.Parameters["in_planid"].Value = htTransData["planid"]; Cmd.Parameters.Add("in_flag", OracleType.VarChar, 10); Cmd.Parameters["in_flag"].Value = htTransData["flag"]; Cmd.Parameters.Add("in_expdate", OracleType.VarChar, 50); Cmd.Parameters["in_expdate"].Value = htTransData["expdate"]; Cmd.Parameters.Add("in_actidate", OracleType.VarChar, 50); Cmd.Parameters["in_actidate"].Value = htTransData["actidate"]; Cmd.Parameters.Add("in_request", OracleType.VarChar, 1000); Cmd.Parameters["in_request"].Value = htTransData["request"]; Cmd.Parameters.Add("in_IP", OracleType.VarChar, 1000); Cmd.Parameters["in_IP"].Value = htTransData["IP"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 1000); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); string exeData = Convert.ToString(Cmd.Parameters["out_data"].Value); string exeCode = Convert.ToString(Cmd.Parameters["out_errcode"].Value); return(exeCode + "$" + exeData); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(htTransData["username"].ToString(), ex.Message.ToString(), "Cl_Data_TransHwayBulkOperation-validateBulkTrans"); return("-300$ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public string ProvECS(Hashtable ht) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection ConObj = new OracleConnection(ConStr); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_ecs_ins", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar); Cmd.Parameters["in_username"].Value = ht["username"]; Cmd.Parameters.Add("in_lcoid", OracleType.Number); Cmd.Parameters["in_lcoid"].Value = ht["lcoid"]; Cmd.Parameters.Add("in_custid", OracleType.VarChar); Cmd.Parameters["in_custid"].Value = ht["custid"]; Cmd.Parameters.Add("in_vcid", OracleType.VarChar); Cmd.Parameters["in_vcid"].Value = ht["vcid"]; Cmd.Parameters.Add("in_custname", OracleType.VarChar); Cmd.Parameters["in_custname"].Value = ht["custname"]; Cmd.Parameters.Add("in_planid", OracleType.VarChar); Cmd.Parameters["in_planid"].Value = ht["planid"]; Cmd.Parameters.Add("in_flag", OracleType.VarChar); Cmd.Parameters["in_flag"].Value = ht["flag"]; Cmd.Parameters.Add("in_expdate", OracleType.VarChar); Cmd.Parameters["in_expdate"].Value = ht["expdate"]; Cmd.Parameters.Add("in_actidate", OracleType.VarChar); Cmd.Parameters["in_actidate"].Value = ht["actidate"]; Cmd.Parameters.Add("in_obrmsts", OracleType.VarChar); Cmd.Parameters["in_obrmsts"].Value = ht["obrmsts"]; Cmd.Parameters.Add("in_address", OracleType.VarChar); Cmd.Parameters["in_address"].Value = ht["cust_addr"]; Cmd.Parameters.Add("in_IP", OracleType.VarChar); Cmd.Parameters["in_IP"].Value = ht["IP"]; Cmd.Parameters.Add("in_autorenew", OracleType.VarChar); Cmd.Parameters["in_autorenew"].Value = ht["autorenew"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 500); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); string ExeResult = Convert.ToString(Cmd.Parameters["out_errcode"].Value); string ExeResultMsg = Convert.ToString(Cmd.Parameters["out_data"].Value); ConObj.Dispose(); return(ExeResult + "$" + ExeResultMsg); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(ht["username"].ToString(), ex.Message.ToString(), "Cls_Data_TransHwayBulkOperation.cs-ProvECS"); return("-310$ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public string[] GetLcoDetails(string username, string prefixText, string type, string operid, string catid) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection conObj = new OracleConnection(ConStr); try { string str = ""; str = " SELECT a.operid, a.lcoid, a.distid, a.parentid, a.hoid, a.lcocode, a.lconame, "; str += " a.var_lcomst_address, a.num_lcomst_mobileno, a.var_lcomst_email, "; str += " a.awailbal, a.state, a.city, a.dist, a.subdist, a.jvno, a.erpaccno, a.ecsstatus, "; str += " a.AREAMANAGER, a.PTEXPIRYDT, a.INTAGREEEXPDT, a.EXECUTIVE "; str += " FROM view_lcoall_det_summ a "; if (type == "1") { str += " where upper(a.lconame) like upper('" + prefixText + "') "; } else if (type == "0") { str += " where upper(a.lcocode) like upper('" + prefixText + "')"; } if (catid == "2") { str += " and a.parentid = " + operid; } else if (catid == "5") { str += " and a.distid = " + operid; } else if (catid == "10") { str += " and a.HOID = " + operid; } OracleCommand cmd = new OracleCommand(str, conObj); conObj.Open(); List <string> Operators = new List <string>(); OracleDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Operators.Add(dr["lcocode"].ToString()); Operators.Add(dr["lconame"].ToString()); Operators.Add(dr["var_lcomst_address"].ToString()); Operators.Add(dr["num_lcomst_mobileno"].ToString()); Operators.Add(dr["var_lcomst_email"].ToString()); Operators.Add(dr["awailbal"].ToString()); Operators.Add(dr["lcoid"].ToString()); Operators.Add(dr["operid"].ToString()); Operators.Add(dr["state"].ToString()); Operators.Add(dr["city"].ToString()); Operators.Add(dr["dist"].ToString()); Operators.Add(dr["subdist"].ToString()); Operators.Add(dr["jvno"].ToString()); Operators.Add(dr["erpaccno"].ToString()); Operators.Add(dr["ecsstatus"].ToString()); Operators.Add(dr["AREAMANAGER"].ToString()); Operators.Add(dr["PTEXPIRYDT"].ToString()); Operators.Add(dr["INTAGREEEXPDT"].ToString()); Operators.Add(dr["EXECUTIVE"].ToString()); } string[] prefixTextArray = Operators.ToArray <string>(); conObj.Close(); return(prefixTextArray); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_RptLcoAll-GetLcoDetails"); return(null); } finally { conObj.Close(); conObj.Dispose(); } }
public DataTable GetPriceDetails(Hashtable htAddPlanParams, string username, string operid, string catid) { try { string from = htAddPlanParams["from"].ToString(); string to = htAddPlanParams["to"].ToString(); string package = htAddPlanParams["package"].ToString(); string plan_name = "All"; string search_type = htAddPlanParams["search"].ToString().Trim(); string txtsearch = htAddPlanParams["txtsearch"].ToString().Trim(); if (htAddPlanParams["plan_name"] != null) { //in case of expiry report, this will be null plan_name = htAddPlanParams["plan_name"].ToString(); } ; Cls_Helper ObjHelper = new Cls_Helper(); string StrQry; StrQry = " (SELECT sum(a.lco_price) lcoprice,sum(a.Cust_price) CustPrice FROM (SELECT a.plan_poid, account_no, first_name, middle_name, last_name, "; StrQry += " address, vc, mobile, lco_code, lco_name, name, enddate, account_poid, product_poid, service_poid, purchase_poid, num_lcomst_cityid city_id, city, var_brm_renewflag, stb,aoup_lcopre_lcoprice(var_lcomst_code, REPLACE(name,'MANDATE CHANNELS 30d','NCF 1 30d')) lco_price,aoup_lcopre_Custprice(var_lcomst_code, REPLACE(name,'MANDATE CHANNELS 30d','NCF 1 30d')) Cust_price "; StrQry += " FROM HWCAS_BRM_CUST_MASTER a, aoup_lcopre_lco_Det b, aoup_lcopre_city_def c WHERE TRUNC (enddate) BETWEEN (SYSDATE - 15) AND (SYSDATE + 15) AND a.lco_code = b.var_lcomst_code "; StrQry += " AND b.num_lcomst_cityid = c.num_city_id) a, (SELECT DISTINCT var_plan_planpoid, num_plan_cityid, var_plan_plantype FROM aoup_lcopre_plan_def) b, aoup_lcopre_lco_det c "; if (catid == "10") { StrQry += " ,aoup_operator_def j,aoup_operator_def k"; } StrQry += " where a.plan_poid = b.var_plan_planpoid and a.lco_code=c.var_lcomst_code and b.num_plan_cityid=a.city_id"; if (catid == "10") { StrQry += " and c.num_lcomst_operid=j.num_oper_id and j.num_oper_parentid=k.num_oper_id"; } StrQry += " and trunc(a.enddate) >='" + from + "'"; StrQry += " and trunc(a.enddate) <='" + to + "'"; if (plan_name.Trim() != "All") { StrQry += " and trim(a.name) = trim('" + plan_name + "')"; } if (package != "All") { StrQry += " and b.var_plan_plantype='" + package.ToString() + "'"; } if (catid == "3" || catid == "11") { StrQry += " and c.num_lcomst_operid = '" + operid + "'"; } else if (catid == "10") { StrQry += " and k.num_oper_parentid = '6476'"; } if (txtsearch.Length > 0) { if (search_type == "0") { StrQry += @" and upper(a.account_no)= upper('" + txtsearch + "')"; } else if (search_type == "1") { StrQry += @" and upper(a.vc)= upper('" + txtsearch + "')"; } else if (search_type == "2") { StrQry += @" and a.lco_code= '" + txtsearch + "'"; } } StrQry += ")"; // FileLogText("Expiry Query : ", "", StrQry, htAddPlanParams["plan_name"].ToString()); return(ObjHelper.GetDataTable(StrQry)); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_rptExpiry.cs-GetDetails"); return(null); } }
public DataTable GetExpDetails(Hashtable htAddPlanParams, string username, string operid, string catid, string type, string planname) { try { string from = htAddPlanParams["from"].ToString(); string to = htAddPlanParams["to"].ToString(); string plan_name = "ALL"; string vcid = ""; if (htAddPlanParams["plan_name"] != null) { //in case of expiry report, this will be null plan_name = htAddPlanParams["plan_name"].ToString(); } if (htAddPlanParams["vcid"].ToString() != "" || htAddPlanParams["vcid"] != null) { vcid = htAddPlanParams["vcid"].ToString(); } Cls_Helper ObjHelper = new Cls_Helper(); string StrQry; StrQry = "SELECT a.account_no, a.fullname, a.address, a.vc, a.mobile, a.lco_code, a.lco_name, a.planname, a.plantype, to_char(a.enddate, 'dd-Mon-yyyy') enddate, a.account_poid," + " a.product_poid, a.service_poid, a.purchase_poid,a.cityname, a.brmpoid, trunc(a.prodenddate) prodenddate" + " FROM view_lcopre_expiry a " + " where (a.enddate >='" + from + "' " + " and a.enddate <='" + to + "' or " + " a.prodenddate >='" + from + "' " + " and a.prodenddate <='" + to + "')"; if (plan_name.Trim() != "ALL") { StrQry += " and upper(trim(a.planname)) = upper(trim('" + plan_name + "'))"; } if (vcid != "") { StrQry += " and a.vc='" + vcid + "' "; } //StrQry += " and freeflag='N' "; if (type != "ALL") { string plan_type = "Al-la-carte"; if (type == "AD") { plan_type = "Addon"; } else if (type == "B,HSP") { plan_type = "'Basic','HSP'"; } StrQry += " and trim(a.plantype) in (" + plan_type + ")"; } if (catid == "3" || catid == "11") { StrQry += " and a.operid = '" + operid + "'"; } else if (catid == "10") { StrQry += " and a.hoid = '" + operid + "'"; } FileLogText("On Submit : ", "", "", StrQry, ""); return(ObjHelper.GetDataTable(StrQry)); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_data_TransHwayBulkRenewal.cs-GetExpDetails"); return(null); } }
public string BulkRenewMoveData(string username, string uploadid, string filename, string fromdt, string todt, string plan, string plan_type, string vcid) { OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim()); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_bulk_renewal2_emp", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 100); Cmd.Parameters["in_username"].Value = username; DateTime fromDt = DateTime.ParseExact(fromdt, "dd-MMM-yyyy", null); DateTime toDt = DateTime.ParseExact(todt, "dd-MMM-yyyy", null); Cmd.Parameters.Add("in_fromdt", OracleType.DateTime); Cmd.Parameters["in_fromdt"].Value = fromDt; Cmd.Parameters.Add("in_todate", OracleType.DateTime); Cmd.Parameters["in_todate"].Value = toDt; Cmd.Parameters.Add("in_useruniquekey", OracleType.VarChar, 100); Cmd.Parameters["in_useruniquekey"].Value = uploadid; Cmd.Parameters.Add("in_filename", OracleType.VarChar, 100); Cmd.Parameters["in_filename"].Value = filename; Cmd.Parameters.Add("in_plan", OracleType.VarChar, 1000); Cmd.Parameters["in_plan"].Value = plan; Cmd.Parameters.Add("in_plantype", OracleType.VarChar, 1000); Cmd.Parameters["in_plantype"].Value = plan_type; Cmd.Parameters.Add("in_vcid", OracleType.VarChar, 1000); Cmd.Parameters["in_vcid"].Value = vcid; Cmd.Parameters.Add("out_data", OracleType.VarChar, 250); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value); string Str; if (exeResult == 9999) { Str = "9999$renewal data moved successfully..."; } else { Str = "0$" + Convert.ToString(Cmd.Parameters["out_data"].Value); } ConObj.Dispose(); return(Str); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_data_TransHwayBulkRenewal-BulkRenewMoveData"); return("ex_occured - $ " + ex.Message.ToString()); } finally { ConObj.Close(); ConObj.Dispose(); } }
public DataTable GetTransationsDetLCO(Hashtable ht, string whereClauseStr1, string username1) { OracleConnection ConObj = new OracleConnection(ConStr); try { //Cls_Helper ObjHelper = new Cls_Helper(); //string StrQry; //StrQry = "SELECT lconame, lcocode, sum(openinbal) openinbal, sum(drlimit) drlimit, sum(crlimit) crlimit, sum(closingbal) closingbal " + // " FROM view_LCO_partyledgr_summ " + //" where " + whereClauseStr1 + //" group by lconame, lcocode "; //return ObjHelper.GetDataTable(StrQry); ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_invprtyledsumm_dt", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 50); Cmd.Parameters["in_username"].Value = username1; Cmd.Parameters.Add("in_from_dt", OracleType.VarChar, 100); Cmd.Parameters["in_from_dt"].Value = ht["from"].ToString(); Cmd.Parameters.Add("in_to_dt", OracleType.VarChar, 100); Cmd.Parameters["in_to_dt"].Value = ht["to"].ToString(); Cmd.Parameters.Add("in_lcoid", OracleType.VarChar, 100); Cmd.Parameters["in_lcoid"].Value = ht["lcoid"].ToString(); Cmd.Parameters.Add("out_data", OracleType.VarChar, 4000); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); string exeData = Convert.ToString(Cmd.Parameters["out_data"].Value); string exeCode = Convert.ToString(Cmd.Parameters["out_errcode"].Value); DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("lcocode")); dt.Columns.Add(new DataColumn("lconame")); dt.Columns.Add(new DataColumn("openinbal")); dt.Columns.Add(new DataColumn("drlimit")); dt.Columns.Add(new DataColumn("crlimit")); dt.Columns.Add(new DataColumn("closingbal")); if (exeData != "no_data_found") { DataRow dr = dt.NewRow(); dr["lcocode"] = exeData.Split('$')[0]; dr["lconame"] = exeData.Split('$')[1]; dr["openinbal"] = exeData.Split('$')[2]; dr["crlimit"] = exeData.Split('$')[3]; dr["drlimit"] = exeData.Split('$')[4]; dr["closingbal"] = exeData.Split('$')[5]; dt.Rows.Add(dr); } return(dt); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username1, ex.Message.ToString() + '$' + ht["from"] + '$' + ht["to"] + '$' + ht["lcoid"], "Cls_Data_RptINVLedger.cs-GetTransationsDetLCO"); return(null); } }
public string setLCOData(string username, Hashtable ht) { OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim()); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_lco_ins_new", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 10); Cmd.Parameters["in_username"].Value = username; Cmd.Parameters.Add("in_lcouser_id", OracleType.VarChar, 100); Cmd.Parameters["in_lcouser_id"].Value = ht["userid"]; Cmd.Parameters.Add("in_lconame", OracleType.VarChar, 100); Cmd.Parameters["in_lconame"].Value = ht["name"]; Cmd.Parameters.Add("in_lcofirstname", OracleType.VarChar, 100); Cmd.Parameters["in_lcofirstname"].Value = ht["fname"]; Cmd.Parameters.Add("in_lcomiddlename", OracleType.VarChar, 100); Cmd.Parameters["in_lcomiddlename"].Value = ht["mname"]; Cmd.Parameters.Add("in_lcolastname", OracleType.VarChar, 100); Cmd.Parameters["in_lcolastname"].Value = ht["lname"]; Cmd.Parameters.Add("in_lcoaddress", OracleType.VarChar, 500); Cmd.Parameters["in_lcoaddress"].Value = ht["addr"]; Cmd.Parameters.Add("in_lcopin", OracleType.Number); Cmd.Parameters["in_lcopin"].Value = Convert.ToInt64(ht["pin"]); Cmd.Parameters.Add("in_lcocode", OracleType.VarChar, 100); Cmd.Parameters["in_lcocode"].Value = ht["code"]; Cmd.Parameters.Add("in_lcostateid", OracleType.Number); Cmd.Parameters["in_lcostateid"].Value = Convert.ToInt64(ht["state"]); Cmd.Parameters.Add("in_lcocityid", OracleType.Number); Cmd.Parameters["in_lcocityid"].Value = Convert.ToInt64(ht["city"]); Cmd.Parameters.Add("in_lcoemail", OracleType.VarChar, 100); Cmd.Parameters["in_lcoemail"].Value = ht["email"]; Cmd.Parameters.Add("in_lcophone", OracleType.Number); Cmd.Parameters["in_lcophone"].Value = Convert.ToInt64(ht["mobile"]); Cmd.Parameters.Add("in_lcojvno", OracleType.Number); Cmd.Parameters["in_lcojvno"].Value = Convert.ToInt64(ht["jv"]); Cmd.Parameters.Add("in_lcodirectno", OracleType.Number); Cmd.Parameters["in_lcodirectno"].Value = Convert.ToInt64(ht["direct"]); Cmd.Parameters.Add("in_lcobrmpoid", OracleType.VarChar, 100); Cmd.Parameters["in_lcobrmpoid"].Value = ht["brmpoid"]; Cmd.Parameters.Add("in_lcocompany", OracleType.VarChar, 100); Cmd.Parameters["in_lcocompany"].Value = ht["company"]; Cmd.Parameters.Add("in_lcodist", OracleType.VarChar, 100); Cmd.Parameters["in_lcodist"].Value = ht["distributor"]; Cmd.Parameters.Add("in_lcosubdist", OracleType.VarChar, 100); Cmd.Parameters["in_lcosubdist"].Value = ht["subdistributor"]; // Cmd.Parameters.Add("in_lcosubdist", OracleType.VarChar, 100); // Cmd.Parameters["in_lcosubdist"].Value = ht["subdistributor"]; Cmd.Parameters.Add("in_lcocompcode", OracleType.VarChar, 100); Cmd.Parameters["in_lcocompcode"].Value = ht["compcode"]; Cmd.Parameters.Add("in_insflag", OracleType.VarChar, 10); Cmd.Parameters["in_insflag"].Value = ht["flag"]; Cmd.Parameters.Add("in_lcooperid", OracleType.VarChar, 100); Cmd.Parameters["in_lcooperid"].Value = ht["lcoid"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 500); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("in_parentid", OracleType.Number); Cmd.Parameters["in_parentid"].Value = 0; Cmd.Parameters.Add("in_distid", OracleType.VarChar, 100); Cmd.Parameters["in_distid"].Value = 0; Cmd.Parameters.Add("in_user", OracleType.VarChar, 100); Cmd.Parameters["in_user"].Value = ht["LcoUserName"]; Cmd.Parameters.Add("in_ecsstatus", OracleType.VarChar, 2); Cmd.Parameters["in_ecsstatus"].Value = ht["ecssattus"]; Cmd.ExecuteNonQuery(); ConObj.Close(); int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value); string Str; if (exeResult == 9999) { if (Convert.ToInt32(ht["flag"]) == 0) { Str = " LCO registered successfully..."; } else { Str = " LCO updated successfully..."; } } else { Str = Convert.ToString(Cmd.Parameters["out_data"].Value); } ConObj.Dispose(); return(Str); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstLCORegistration.cs-setLCOData"); return("ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public DataSet getCompanyData(string username, string catid, string operid, string companyname) { try { DataTable dtCompany = new DataTable("COMPANY"); //DataTable dtDist = new DataTable("DISTRIBUTOR"); //DataTable dtSubDist = new DataTable("SUBDISTRIBUTOR"); //DataTable dtMso = new DataTable("MSO"); //DataTable dtDistributor = new DataTable("Dist"); Cls_Helper objHelper = new Cls_Helper(); string strCompQry = "SELECT a.num_comp_companyid COMP_ID, a.var_comp_companyname COMP_NAME FROM aoup_lcopre_company_det a where a.var_comp_companyname='" + companyname + "'"; //string strDistQry = "SELECT a.num_comp_distid DIST_ID, a.var_comp_distname DIST_NAME FROM aoup_lcopre_company_det a"; //string strSubDistQry = "SELECT a.num_comp_subdistid SUBDIST_ID, a.var_comp_subdistname SUBDIST_NAME FROM aoup_lcopre_company_det a"; //string strMso = ""; //string strDist = ""; //if (catid == "2") //{ // strMso += "select num_oper_id,var_oper_opername from aoup_operator_Def "; // strMso += " where num_oper_id='" + operid + "'"; // strMso += " and num_oper_category=2"; // strMso += " order by var_oper_opername asc "; // strDist = "select a.num_oper_id,a.var_oper_opername from aoup_operator_Def a,aoup_operator_Def b"; // strDist += " where b.num_oper_id='" + operid + "'"; // strDist += " and a.num_oper_category=5"; // strDist += " and a.num_oper_parentid=b.num_oper_id"; // strDist += " order by var_oper_opername asc "; //} //else if (catid == "5") //{ // strDist = "select num_oper_id,var_oper_opername from aoup_operator_Def"; // strDist += " where num_oper_id='" + operid + "'"; // strDist += " and num_oper_category=5"; // strDist += " order by var_oper_opername asc "; // strMso += "select b.num_oper_id,b.var_oper_opername from aoup_operator_Def a,aoup_operator_Def b"; // strMso += " where a.num_oper_id='" + operid + "'"; // strMso += " and a.num_oper_category=5"; // strMso += " and a.num_oper_parentid=b.num_oper_id"; // strMso += " order by b.var_oper_opername asc "; //} //else //{ // strMso += "select num_oper_id,var_oper_opername from aoup_operator_Def "; // strMso += " where num_oper_category=2"; // strMso += " order by var_oper_opername asc "; // strDist = "select num_oper_id,var_oper_opername from aoup_operator_Def"; // strDist += " where num_oper_category=5"; // strDist += " order by var_oper_opername asc "; //} dtCompany = objHelper.GetDataTable(strCompQry); //dtDist = objHelper.GetDataTable(strDistQry); //dtSubDist = objHelper.GetDataTable(strSubDistQry); //dtMso = objHelper.GetDataTable(strMso); //dtDistributor = objHelper.GetDataTable(strDist); DataSet dsCompData = new DataSet(); dsCompData.Tables.Add(dtCompany); //dsCompData.Tables.Add(dtDist); //dsCompData.Tables.Add(dtSubDist); //dsCompData.Tables.Add(dtMso); //dsCompData.Tables.Add(dtDistributor); return(dsCompData); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstLCORegistration.cs-getCompanyData"); return(null); } }
public string setUserData(string username, Hashtable ht) { OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim()); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_user_ins", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 100); Cmd.Parameters["in_username"].Value = username; Cmd.Parameters.Add("in_userid", OracleType.VarChar, 100); Cmd.Parameters["in_userid"].Value = ht["userid12"]; Cmd.Parameters.Add("in_userowner", OracleType.VarChar, 100); Cmd.Parameters["in_userowner"].Value = ht["userowner"]; Cmd.Parameters.Add("in_password1", OracleType.VarChar, 100); Cmd.Parameters["in_password1"].Value = "a"; Cmd.Parameters.Add("in_password2", OracleType.VarChar, 100); Cmd.Parameters["in_password2"].Value = "a"; Cmd.Parameters.Add("in_firstname", OracleType.VarChar, 100); Cmd.Parameters["in_firstname"].Value = ht["fname"]; Cmd.Parameters.Add("in_middlename", OracleType.VarChar, 500); Cmd.Parameters["in_middlename"].Value = ht["mname"]; Cmd.Parameters.Add("in_lastname", OracleType.VarChar, 100); Cmd.Parameters["in_lastname"].Value = ht["lname"]; Cmd.Parameters.Add("in_address", OracleType.VarChar, 100); Cmd.Parameters["in_address"].Value = ht["addr"]; Cmd.Parameters.Add("in_pincode", OracleType.VarChar, 100); Cmd.Parameters["in_pincode"].Value = ht["pincode"]; Cmd.Parameters.Add("in_brmpoid", OracleType.VarChar, 100); Cmd.Parameters["in_brmpoid"].Value = ht["brmpoid"]; Cmd.Parameters.Add("in_stateid", OracleType.Number); Cmd.Parameters["in_stateid"].Value = Convert.ToInt64(ht["state"]); Cmd.Parameters.Add("in_cityid", OracleType.Number); Cmd.Parameters["in_cityid"].Value = Convert.ToInt64(ht["city"]); Cmd.Parameters.Add("in_email", OracleType.VarChar, 100); Cmd.Parameters["in_email"].Value = ht["email"]; Cmd.Parameters.Add("in_mobileno", OracleType.Number); Cmd.Parameters["in_mobileno"].Value = ht["mobileno"]; Cmd.Parameters.Add("in_compcode", OracleType.VarChar, 100); Cmd.Parameters["in_compcode"].Value = ht["compcode"]; Cmd.Parameters.Add("in_accno", OracleType.VarChar, 100); Cmd.Parameters["in_accno"].Value = ht["accno"]; Cmd.Parameters.Add("in_lcoid", OracleType.Number); Cmd.Parameters["in_lcoid"].Value = ht["lcoid2"]; Cmd.Parameters.Add("in_flag", OracleType.VarChar, 10); Cmd.Parameters["in_flag"].Value = ht["userlevel"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 500); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value); string Str; if (exeResult == 9999) { if (Convert.ToInt32(ht["flag"]) == 0) { Str = " USER registered successfully..."; } else { Str = " USER updated successfully..."; } } else { Str = Convert.ToString(Cmd.Parameters["out_data"].Value); } ConObj.Dispose(); return(Str); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstLCOPreUDefeine.cs-setUserData"); return("ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public string LCOAssignRights(string username, Hashtable ht) { OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim()); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("AOUP_LCOPRE_ACCESS_CTRL_INST", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 100); Cmd.Parameters["in_username"].Value = username; if (ht["LCOCode"].ToString() != "" && ht["LCOCode"].ToString() != null) { Cmd.Parameters.Add("in_LCOCode", OracleType.VarChar, 100); Cmd.Parameters["in_LCOCode"].Value = ht["LCOCode"].ToString(); } else { Cmd.Parameters.Add("in_LCOCode", OracleType.VarChar, 100); Cmd.Parameters["in_LCOCode"].Value = DBNull.Value; } if (ht["UserAccMap"].ToString() != "" && ht["UserAccMap"].ToString() != null) { Cmd.Parameters.Add("in_UserAccMap", OracleType.VarChar, 100); Cmd.Parameters["in_UserAccMap"].Value = ht["UserAccMap"].ToString(); } else { Cmd.Parameters.Add("in_UserAccMap", OracleType.VarChar, 100); Cmd.Parameters["in_UserAccMap"].Value = DBNull.Value; } if (ht["Add"].ToString() != "" && ht["Add"].ToString() != null) { Cmd.Parameters.Add("in_Add", OracleType.VarChar, 100); Cmd.Parameters["in_Add"].Value = ht["Add"].ToString(); } else { Cmd.Parameters.Add("in_Add", OracleType.VarChar, 100); Cmd.Parameters["in_Add"].Value = DBNull.Value; } if (ht["Renew"].ToString() != "" && ht["Renew"].ToString() != null) { Cmd.Parameters.Add("in_renew", OracleType.VarChar, 100); Cmd.Parameters["in_renew"].Value = ht["Renew"].ToString(); } else { Cmd.Parameters.Add("in_renew", OracleType.VarChar, 100); Cmd.Parameters["in_renew"].Value = DBNull.Value; } if (ht["Change"].ToString() != "" && ht["Change"].ToString() != null) { Cmd.Parameters.Add("in_Change", OracleType.VarChar, 100); Cmd.Parameters["in_Change"].Value = ht["Change"].ToString(); } else { Cmd.Parameters.Add("in_Change", OracleType.VarChar, 100); Cmd.Parameters["in_Change"].Value = DBNull.Value; } if (ht["Cancel"].ToString() != "" && ht["Cancel"].ToString() != null) { Cmd.Parameters.Add("in_Cancel", OracleType.VarChar, 100); Cmd.Parameters["in_Cancel"].Value = ht["Cancel"].ToString(); } else { Cmd.Parameters.Add("in_Cancel", OracleType.VarChar, 100); Cmd.Parameters["in_Cancel"].Value = DBNull.Value; } if (ht["Discount"].ToString() != "" && ht["Discount"].ToString() != null) { Cmd.Parameters.Add("in_Discount", OracleType.VarChar, 100); Cmd.Parameters["in_Discount"].Value = ht["Discount"].ToString(); } else { Cmd.Parameters.Add("in_Discount", OracleType.VarChar, 100); Cmd.Parameters["in_Discount"].Value = DBNull.Value; } if (ht["Retrack"].ToString() != "" && ht["Retrack"].ToString() != null) { Cmd.Parameters.Add("in_Retrack", OracleType.VarChar, 100); Cmd.Parameters["in_Retrack"].Value = ht["Retrack"].ToString(); } else { Cmd.Parameters.Add("in_Retrack", OracleType.VarChar, 100); Cmd.Parameters["in_Retrack"].Value = DBNull.Value; } if (ht["CustModify"].ToString() != "" && ht["CustModify"].ToString() != null) { Cmd.Parameters.Add("in_CustModify", OracleType.VarChar, 100); Cmd.Parameters["in_CustModify"].Value = ht["CustModify"].ToString(); } else { Cmd.Parameters.Add("in_CustModify", OracleType.VarChar, 100); Cmd.Parameters["in_CustModify"].Value = DBNull.Value; } if (ht["STBSwap"].ToString() != "" && ht["STBSwap"].ToString() != null) { Cmd.Parameters.Add("in_STBSwap", OracleType.VarChar, 100); Cmd.Parameters["in_STBSwap"].Value = ht["STBSwap"].ToString(); } else { Cmd.Parameters.Add("in_STBSwap", OracleType.VarChar, 100); Cmd.Parameters["in_STBSwap"].Value = DBNull.Value; } if (ht["AutoRenew"].ToString() != "" && ht["AutoRenew"].ToString() != null) { Cmd.Parameters.Add("in_AutoRenew", OracleType.VarChar, 100); Cmd.Parameters["in_AutoRenew"].Value = ht["AutoRenew"].ToString(); } else { Cmd.Parameters.Add("in_AutoRenew", OracleType.VarChar, 100); Cmd.Parameters["in_AutoRenew"].Value = DBNull.Value; } if (ht["Deactivate"].ToString() != "" && ht["Deactivate"].ToString() != null) { Cmd.Parameters.Add("in_Deactivate", OracleType.VarChar, 100); Cmd.Parameters["in_Deactivate"].Value = ht["Deactivate"].ToString(); } else { Cmd.Parameters.Add("in_Deactivate", OracleType.VarChar, 100); Cmd.Parameters["in_Deactivate"].Value = DBNull.Value; } if (ht["Terminate"].ToString() != "" && ht["Terminate"].ToString() != null) { Cmd.Parameters.Add("in_Terminate", OracleType.VarChar, 100); Cmd.Parameters["in_Terminate"].Value = ht["Terminate"].ToString(); } else { Cmd.Parameters.Add("in_Terminate", OracleType.VarChar, 100); Cmd.Parameters["in_Terminate"].Value = DBNull.Value; } if (ht["FocPack"].ToString() != "" && ht["FocPack"].ToString() != null) { Cmd.Parameters.Add("in_FOCPack", OracleType.VarChar, 100); Cmd.Parameters["in_FOCPack"].Value = ht["FocPack"].ToString(); } else { Cmd.Parameters.Add("in_FOCPack", OracleType.VarChar, 100); Cmd.Parameters["in_FOCPack"].Value = DBNull.Value; } if (ht["Pages"].ToString() != "" && ht["Pages"].ToString() != null) { Cmd.Parameters.Add("in_Page", OracleType.VarChar, 100); Cmd.Parameters["in_Page"].Value = ht["Pages"].ToString(); } else { Cmd.Parameters.Add("in_Page", OracleType.VarChar, 100); Cmd.Parameters["in_Page"].Value = DBNull.Value; } Cmd.Parameters.Add("out_data", OracleType.VarChar, 500); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value); string Str; if (exeResult == 9999) { Str = " LCO Rights Submitted successfully..."; } else { Str = Convert.ToString(Cmd.Parameters["out_data"].Value); } ConObj.Dispose(); return(Str); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstLCOUpdateDetails.cs-LCOAssignRights"); return("ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public DataTable GetDetails(Hashtable htAddPlanParams, string username, string operid, string catid) { try { string from = htAddPlanParams["from"].ToString(); string to = htAddPlanParams["to"].ToString(); string package = htAddPlanParams["package"].ToString(); string plan_name = "All"; string search_type = htAddPlanParams["search"].ToString().Trim(); string txtsearch = htAddPlanParams["txtsearch"].ToString().Trim(); if (htAddPlanParams["plan_name"] != null) { //in case of expiry report, this will be null plan_name = htAddPlanParams["plan_name"].ToString(); } ; Cls_Helper ObjHelper = new Cls_Helper(); string StrQry; //StrQry = "SELECT a.account_no, a.fullname, a.address, a.vc, a.mobile, a.lco_code, a.lco_name, a.planname, a.plantype, to_char(a.enddate, 'dd-Mon-yyyy') enddate, a.account_poid," + // " a.product_poid, a.service_poid, a.purchase_poid,a.cityname, a.brmpoid , a.renewflagstatus " + // " FROM view_lcopre_expiry_rpt a " + // " where trunc(a.enddate) >='" + from + "' " + // " and trunc(a.enddate) <='" + to + "'"; //if (plan_name.Trim() != "All") { // StrQry += " and trim(a.planname) = trim('" + plan_name + "')"; //} //if (package != "All") //{ // StrQry += " and PACKAGETYPE='"+package.ToString()+"'"; //} //if (catid == "3") //{ // StrQry += " and a.operid = '" + operid + "'"; //} //else if (catid == "10") //{ // StrQry += " and a.hoid = '" + operid + "'"; //} //if (txtsearch.Length > 0) //{ // if (search_type == "0") // { // StrQry += @" and upper(a.account_no)= upper('" + txtsearch + "')"; // } // else if (search_type == "1") // { // StrQry += @" and upper(a.vc)= upper('" + txtsearch + "')"; // } // else if (search_type == "2") // { // StrQry += @" and upper(a.lco_code)= upper('" + txtsearch + "')"; // } //} //StrQry = "( select rownum as rnk,(a.account_no) account_no,6476 hoid,c.num_lcomst_operid operid,a.first_name||' '||a.middle_name||' '||a.last_name fullname, "; //StrQry += " a.address address,a.vc vc,a.mobile mobile,a.lco_code lco_code,a.lco_name lco_name,a.name planname,(case when b.var_plan_plantype = 'B' then 'Basic' when b.var_plan_plantype = 'AL' then 'Al-la-carte' "; //StrQry += " when b.var_plan_plantype = 'AD' then 'Addon' else b.var_plan_plantype end) plantype,var_plan_plantype packagetype,TO_CHAR(a.enddate,'DD/MM/YYYY') enddate,a.account_poid account_poid, "; //StrQry += " a.product_poid product_poid, a.service_poid service_poid,a.purchase_poid purchase_poid,a.city cityname,c.var_lcomst_brmpoid BRMPOID,a.var_brm_renewflag RenewFlagStatus "; ///* //StrQry += " from ( select account_no,first_name,middle_name,last_name,"; //StrQry += " address,vc,mobile,lco_code,lco_name,name,enddate,account_poid,product_poid,"; //StrQry += " service_poid,purchase_poid,(select num_city_id from aoup_lcopre_city_def where var_city_name=city )city_id,city,"; //StrQry += " var_brm_renewflag from HWCAS_BRM_CUST_MASTER where enddate between (sysdate-15) and (sysdate+15)"; //StrQry += " )a, (select distinct var_plan_productpoid,num_plan_cityid,var_plan_plantype from aoup_lcopre_plan_def "; //StrQry += " ) b,aoup_lcopre_lco_det c ";*/ ////Commented By Sandeep on 20_Jul_2016 //StrQry += " from ( select account_no,first_name,middle_name,last_name, "; //StrQry += " address,vc,mobile,lco_code,lco_name,name,enddate,account_poid,product_poid, "; //StrQry += " service_poid,purchase_poid,num_lcomst_cityid city_id,city, "; //StrQry += " var_brm_renewflag from HWCAS_BRM_CUST_MASTER a,aoup_lcopre_lco_Det b,aoup_lcopre_city_def c "; //StrQry += " where trunc(enddate) between (sysdate-15) and (sysdate+15) "; //StrQry += " and a.lco_code=b.var_lcomst_code and b.num_lcomst_cityid=c.num_city_id "; //StrQry += " )a, (select distinct var_plan_productpoid,num_plan_cityid,var_plan_plantype from aoup_lcopre_plan_def "; //StrQry += " ) b,aoup_lcopre_lco_det c "; StrQry = " (SELECT ROWNUM AS rnk, (a.account_no) account_no, 6476 hoid,c.num_lcomst_operid operid,a.first_name || ' ' || a.middle_name || ' ' || a.last_name fullname, "; StrQry += " a.address address, a.vc vc, a.mobile mobile, a.lco_code lco_code,a.lco_name lco_name, a.name planname,(CASE WHEN b.var_plan_plantype = 'B' THEN 'Basic' "; StrQry += " WHEN b.var_plan_plantype = 'AL' THEN 'Al-la-carte' WHEN b.var_plan_plantype = 'AD' THEN 'Addon' ELSE b.var_plan_plantype END) plantype, var_plan_plantype packagetype, "; StrQry += " TO_CHAR (a.enddate, 'DD/MM/YYYY') enddate, a.account_poid account_poid, a.product_poid product_poid, a.service_poid service_poid, a.purchase_poid purchase_poid, a.city cityname, "; StrQry += " c.var_lcomst_brmpoid BRMPOID, a.var_brm_renewflag RenewFlagStatus, a.stb stb,a.lco_price FROM (SELECT a.plan_poid, account_no, first_name, middle_name, last_name, "; StrQry += " address, vc, mobile, lco_code, lco_name, name, enddate, account_poid, product_poid, service_poid, purchase_poid, num_lcomst_cityid city_id, city, var_brm_renewflag, stb,aoup_lcopre_lcoprice(var_lcomst_code, REPLACE(name,'MANDATE CHANNELS 30d','NCF 1 30d')) lco_price,aoup_lcopre_custprice(var_lcomst_code, REPLACE(name,'MANDATE CHANNELS 30d','NCF 1 30d')) Cust_price "; StrQry += " FROM HWCAS_BRM_CUST_MASTER a, aoup_lcopre_lco_Det b, aoup_lcopre_city_def c WHERE TRUNC (enddate) BETWEEN (SYSDATE - 15) AND (SYSDATE + 15) AND a.lco_code = b.var_lcomst_code "; StrQry += " AND b.num_lcomst_cityid = c.num_city_id) a, (SELECT DISTINCT var_plan_planpoid, num_plan_cityid, var_plan_plantype FROM aoup_lcopre_plan_def) b, aoup_lcopre_lco_det c "; if (catid == "10") { StrQry += " ,aoup_operator_def j,aoup_operator_def k"; } StrQry += " where a.plan_poid = b.var_plan_planpoid and a.lco_code=c.var_lcomst_code and b.num_plan_cityid=a.city_id"; if (catid == "10") { StrQry += " and c.num_lcomst_operid=j.num_oper_id and j.num_oper_parentid=k.num_oper_id"; } StrQry += " and trunc(a.enddate) >='" + from + "'"; StrQry += " and trunc(a.enddate) <='" + to + "'"; if (plan_name.Trim() != "All") { StrQry += " and trim(a.name) = trim('" + plan_name + "')"; } if (package != "All") { StrQry += " and b.var_plan_plantype='" + package.ToString() + "'"; } if (catid == "3" || catid == "11") { StrQry += " and c.num_lcomst_operid = '" + operid + "'"; } else if (catid == "10") { StrQry += " and k.num_oper_parentid = '6476'"; } if (txtsearch.Length > 0) { if (search_type == "0") { StrQry += @" and upper(a.account_no)= upper('" + txtsearch + "')"; } else if (search_type == "1") { StrQry += @" and upper(a.vc)= upper('" + txtsearch + "')"; } else if (search_type == "2") { StrQry += @" and a.lco_code= '" + txtsearch + "'"; } } StrQry += ")"; // FileLogText("Expiry Query : ", "", StrQry, htAddPlanParams["plan_name"].ToString()); return(ObjHelper.GetDataTable(StrQry)); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_rptExpiry.cs-GetDetails"); return(null); } }
public string UpdateLCOData(string username, string LCOCode, Hashtable ht) { OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim()); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_lcodata_update", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 100); Cmd.Parameters["in_username"].Value = username; Cmd.Parameters.Add("in_lcocode", OracleType.VarChar, 100); Cmd.Parameters["in_lcocode"].Value = LCOCode; Cmd.Parameters.Add("in_email", OracleType.VarChar, 100); Cmd.Parameters["in_email"].Value = ht["email"]; Cmd.Parameters.Add("in_mobileno", OracleType.VarChar, 100); Cmd.Parameters["in_mobileno"].Value = ht["mobileno"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 500); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value); string Str; if (exeResult == 9999) { Str = " LCO Details Update successfully..."; } else { Str = Convert.ToString(Cmd.Parameters["out_data"].Value); } ConObj.Dispose(); return(Str); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstLCOUpdateDetails.cs-UpdateLCOData"); return("ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public string setLcoData(string username, Hashtable ht) { OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim()); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_otherlco_updt", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 100); Cmd.Parameters["in_username"].Value = username; Cmd.Parameters.Add("in_lcocode", OracleType.VarChar, 100); Cmd.Parameters["in_lcocode"].Value = ht["lcocode"]; Cmd.Parameters.Add("in_areamanager", OracleType.VarChar, 100); Cmd.Parameters["in_areamanager"].Value = ht["areamanager"]; Cmd.Parameters.Add("in_ptexpirydt", OracleType.DateTime); Cmd.Parameters["in_ptexpirydt"].Value = ht["ptexdt"]; Cmd.Parameters.Add("in_intagreexpirydt", OracleType.DateTime); Cmd.Parameters["in_intagreexpirydt"].Value = ht["intagreeexpdt"]; Cmd.Parameters.Add("in_executive", OracleType.VarChar, 100); Cmd.Parameters["in_executive"].Value = ht["executive"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 500); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value); string Str; if (exeResult == 9999) { Str = " Lco Details Updated successfully..."; } else { Str = Convert.ToString(Cmd.Parameters["out_data"].Value); } ConObj.Dispose(); return(Str); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstLcoOtherDetails.cs-setLcoData"); return("ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public string InsertLCOConfig(string username, Hashtable ht) { OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim()); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_lcoconfig_ins", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar, 10); Cmd.Parameters["in_username"].Value = username; Cmd.Parameters.Add("in_state", OracleType.VarChar, 4000); Cmd.Parameters["in_state"].Value = ht["in_state"]; Cmd.Parameters.Add("in_city", OracleType.VarChar, 4000); Cmd.Parameters["in_city"].Value = ht["in_city"]; Cmd.Parameters.Add("in_DAS", OracleType.VarChar, 4000); Cmd.Parameters["in_DAS"].Value = ht["in_DAS"]; Cmd.Parameters.Add("in_LCO", OracleType.VarChar, 4000); Cmd.Parameters["in_LCO"].Value = ht["in_LCO"]; Cmd.Parameters.Add("in_adminlevel", OracleType.VarChar, 4000); Cmd.Parameters["in_adminlevel"].Value = ht["in_adminlevel"]; Cmd.Parameters.Add("in_stateid", OracleType.VarChar, 4000); Cmd.Parameters["in_stateid"].Value = ht["in_stateid"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 500); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value); string Str; if (exeResult == 9999) { Str = " Configuration Details Added successfully..."; } else { Str = Convert.ToString(Cmd.Parameters["out_data"].Value); } ConObj.Dispose(); return(exeResult + "$" + Str); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstHwaymsgBrdr-InsertAdminTransfer"); return("ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }
public void bulkStatusUpdt(Hashtable ht) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection ConObj = new OracleConnection(ConStr); try { ConObj.Open(); OracleCommand Cmd = new OracleCommand("aoup_lcopre_bulk_status_updt", ConObj); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("in_username", OracleType.VarChar); Cmd.Parameters["in_username"].Value = ht["username"]; Cmd.Parameters.Add("in_useruniqueid", OracleType.VarChar); Cmd.Parameters["in_useruniqueid"].Value = ht["upload_id"]; Cmd.Parameters.Add("in_custid", OracleType.VarChar); Cmd.Parameters["in_custid"].Value = ht["cust_no"]; Cmd.Parameters.Add("in_errorcode", OracleType.Number); Cmd.Parameters["in_errorcode"].Value = ht["err_code"]; Cmd.Parameters.Add("in_errormsg", OracleType.VarChar); Cmd.Parameters["in_errormsg"].Value = ht["err_msg"]; Cmd.Parameters.Add("in_action", OracleType.VarChar); Cmd.Parameters["in_action"].Value = ht["action"]; Cmd.Parameters.Add("in_planname", OracleType.VarChar); Cmd.Parameters["in_planname"].Value = ht["plan_name"]; Cmd.Parameters.Add("in_vcid", OracleType.VarChar); Cmd.Parameters["in_vcid"].Value = ht["vcid"]; //Cmd.Parameters.Add("in_lcocode", OracleType.VarChar); //Cmd.Parameters["in_lcocode"].Value = ht["lco_code"]; Cmd.Parameters.Add("out_data", OracleType.VarChar, 4000); Cmd.Parameters["out_data"].Direction = ParameterDirection.Output; Cmd.Parameters.Add("out_errcode", OracleType.Number); Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); ConObj.Close(); string ExeResult = Convert.ToString(Cmd.Parameters["out_errcode"].Value); string ExeResultMsg = Convert.ToString(Cmd.Parameters["out_data"].Value); ConObj.Dispose(); //return ExeResult + "$" + ExeResultMsg; } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(ht["username"].ToString(), ex.Message.ToString(), "Cl_Data_TranHwayBulkOperation-bulkStatusUpdt"); //return "-310$ex_occured"; } finally { ConObj.Close(); ConObj.Dispose(); } }
public string sentMailDatains(string username, Hashtable htsent) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection conObj = new OracleConnection(ConStr); try { conObj.Open(); OracleCommand cmd = new OracleCommand("aoup_lcopre_messenger_data", conObj); //procedure ins Sent Mail data cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("in_username", OracleType.VarChar, 100); // cmd.Parameters["in_username"].Value = username; if (htsent["msgto"].ToString() == "") { cmd.Parameters.Add("in_msgto", OracleType.VarChar, 4000); cmd.Parameters["in_msgto"].Value = DBNull.Value; } else { cmd.Parameters.Add("in_msgto", OracleType.VarChar, 4000); cmd.Parameters["in_msgto"].Value = htsent["msgto"]; } if (htsent["msgtoall"].ToString() == "") { cmd.Parameters.Add("in_msgtoall", OracleType.VarChar, 100); cmd.Parameters["in_msgtoall"].Value = DBNull.Value; } else { cmd.Parameters.Add("in_msgtoall", OracleType.VarChar, 100); cmd.Parameters["in_msgtoall"].Value = htsent["msgtoall"]; } if (htsent["msgtoallID"].ToString() == "") { cmd.Parameters.Add("in_msgtoID", OracleType.VarChar, 100); cmd.Parameters["in_msgtoID"].Value = DBNull.Value; } else { cmd.Parameters.Add("in_msgtoID", OracleType.VarChar, 100); cmd.Parameters["in_msgtoID"].Value = htsent["msgtoallID"]; } cmd.Parameters.Add("in_msgsubject", OracleType.VarChar, 1000); cmd.Parameters["in_msgsubject"].Value = htsent["msgsubject"]; cmd.Parameters.Add("in_msgfile", OracleType.VarChar, 500); cmd.Parameters["in_msgfile"].Value = htsent["msgfile"]; cmd.Parameters.Add("in_msgType", OracleType.VarChar, 100); cmd.Parameters["in_msgType"].Value = htsent["msgType"]; cmd.Parameters.Add("in_msgContecnt", OracleType.VarChar, 1000); cmd.Parameters["in_msgContecnt"].Value = htsent["msgContecnt"]; //NewMail cmd.Parameters.Add("in_newMail", OracleType.VarChar, 1000); cmd.Parameters["in_newMail"].Value = htsent["NewMail"]; //NewMail if (htsent["mainid"] != null) { cmd.Parameters.Add("in_mainid", OracleType.Number, 20); cmd.Parameters["in_mainid"].Value = Convert.ToInt32(htsent["mainid"]); } else { cmd.Parameters.Add("in_mainid", OracleType.Number, 20); cmd.Parameters["in_mainid"].Value = 0; } if (htsent["subid"] != null) { cmd.Parameters.Add("in_subid", OracleType.Number, 20); cmd.Parameters["in_subid"].Value = Convert.ToInt32(htsent["subid"]); } else { cmd.Parameters.Add("in_subid", OracleType.Number, 20); cmd.Parameters["in_subid"].Value = 0; } if (htsent["msgtocc"] != null) { cmd.Parameters.Add("in_msgtoCC", OracleType.VarChar, 4000); cmd.Parameters["in_msgtoCC"].Value = htsent["msgtocc"].ToString(); } else { cmd.Parameters.Add("in_msgtoCC", OracleType.VarChar, 4000); cmd.Parameters["in_msgtoCC"].Value = DBNull.Value; } cmd.Parameters.Add("in_deletetype", OracleType.VarChar, 20); cmd.Parameters["in_deletetype"].Value = Convert.ToString(htsent["deletetype"]); cmd.Parameters.Add("in_city", OracleType.Number, 20); cmd.Parameters["in_city"].Value = Convert.ToInt32(htsent["city"]); cmd.Parameters.Add("in_state", OracleType.Number, 20); cmd.Parameters["in_state"].Value = Convert.ToInt32(htsent["state"]); cmd.Parameters.Add("in_deleteby", OracleType.VarChar, 4000); cmd.Parameters["in_deleteby"].Value = htsent["delete"]; if (htsent["repliedby"] != null) { cmd.Parameters.Add("in_replyby", OracleType.VarChar, 4000); cmd.Parameters["in_replyby"].Value = htsent["repliedby"]; } else { cmd.Parameters.Add("in_replyby", OracleType.VarChar, 4000); cmd.Parameters["in_replyby"].Value = DBNull.Value; } cmd.Parameters.Add("out_data", OracleType.VarChar, 4000); cmd.Parameters["out_data"].Direction = ParameterDirection.Output; cmd.Parameters.Add("out_errcode", OracleType.Number, 100); cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); string cd = Convert.ToString(cmd.Parameters["out_data"].Value); string result = Convert.ToString(cmd.Parameters["out_errcode"].Value); conObj.Close(); return(result); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "cls_data_messenger-sentmaildatains"); return(ex.Message); } finally { conObj.Close(); conObj.Dispose(); } }
public string[] GetLcoDetails(string username, string prefixText, string type, string operid, string catid) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection conObj = new OracleConnection(ConStr); try { string str = ""; if (type == "1") { str = " SELECT a.lcoid, a.distid, a.msoid, a.hoid, a.lcomstcode, a.lcomstname, "; str += " a.lcomstaddress, a.lcomstmobileno, a.lcomstemail, "; str += " a.currentcreditlimit,companyname "; str += " FROM veiw_lcopre_paylco_search a "; str += " where upper(a.LCOMSTNAME) like upper('" + prefixText + "%') "; // str = " select a.var_lcomst_code, a.var_lcomst_name, a.var_lcomst_address, a.num_lcomst_mobileno, a.var_lcomst_email " + //" from aoup_lcopre_lco_det a where upper(a.var_lcomst_name) like upper('" + prefixText + "%') " + //" and rownum = '1'"; } else if (type == "0") { str = " SELECT a.lcoid, a.distid, a.msoid, a.hoid, a.lcomstcode, a.lcomstname, "; str += " a.lcomstaddress, a.lcomstmobileno, a.lcomstemail, "; str += " a.currentcreditlimit,companyname "; str += " FROM veiw_lcopre_paylco_search a "; str += " where a.LCOMSTCODE='" + prefixText + "'"; //str = " select a.var_lcomst_code, a.var_lcomst_name, a.var_lcomst_address, a.num_lcomst_mobileno, a.var_lcomst_email " + // " from aoup_lcopre_lco_det a where upper(a.var_lcomst_code) = '" + prefixText + "'" + // " and rownum = '1'"; } if (catid == "2") { str += " and a.msoid = " + operid; } else if (catid == "5") { str += " and a.distid = " + operid; } else if (catid == "10") { str += " and a.HOID = " + operid; } OracleCommand cmd = new OracleCommand(str, conObj); conObj.Open(); List <string> Operators = new List <string>(); OracleDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Operators.Add(dr["LCOMSTCODE"].ToString()); Operators.Add(dr["LCOMSTNAME"].ToString()); Operators.Add(dr["lcomstaddress"].ToString()); Operators.Add(dr["LCOMSTMOBILENO"].ToString()); Operators.Add(dr["LCOMSTEMAIL"].ToString()); Operators.Add(dr["CURRENTCREDITLIMIT"].ToString()); Operators.Add(dr["companyname"].ToString()); } string[] prefixTextArray = Operators.ToArray <string>(); conObj.Close(); return(prefixTextArray); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_TransHwayLcoPayment.cs-GetLcoDetails"); return(null); } finally { conObj.Close(); conObj.Dispose(); } }
/*----------------------------SP-------------------*/ public string InsertNewSTBSP(string sp, Hashtable ht) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection ConObj = new OracleConnection(ConStr); int returnValue = 0; try { OracleCommand cmd = new OracleCommand(sp, ConObj); cmd.CommandType = CommandType.StoredProcedure; if (ht["STBCount"] == null || ht["STBCount"] == "") { cmd.Parameters.Add("IN_SNEWSTB_STBCOUNT", OracleType.Number); cmd.Parameters["IN_SNEWSTB_STBCOUNT"].Value = 0; } else { cmd.Parameters.Add("IN_SNEWSTB_STBCOUNT", OracleType.Number); cmd.Parameters["IN_SNEWSTB_STBCOUNT"].Value = ht["STBCount"]; } if (ht["STBLCO"] == null || ht["STBLCO"] == "") { cmd.Parameters.Add("IN_SNEWSTB_STBLCO", OracleType.VarChar); cmd.Parameters["IN_SNEWSTB_STBLCO"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_SNEWSTB_STBLCO", OracleType.VarChar); cmd.Parameters["IN_SNEWSTB_STBLCO"].Value = ht["STBLCO"]; } if (ht["Remark"] == null || ht["Remark"] == "") { cmd.Parameters.Add("IN_PISNEWSTB_REMARK", OracleType.VarChar); cmd.Parameters["IN_PISNEWSTB_REMARK"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISNEWSTB_REMARK", OracleType.VarChar); cmd.Parameters["IN_PISNEWSTB_REMARK"].Value = ht["Remark"]; } if (ht["SchemeID"] == null || ht["SchemeID"] == "") { cmd.Parameters.Add("IN_SCHEME_ID", OracleType.Number); cmd.Parameters["IN_SCHEME_ID"].Value = 0; } else { cmd.Parameters.Add("IN_SCHEME_ID", OracleType.Number); cmd.Parameters["IN_SCHEME_ID"].Value = Convert.ToInt64(ht["SchemeID"]); } if (ht["STBSRNo"] == null || ht["STBSRNo"] == "") { cmd.Parameters.Add("IN_SNEWSTB_STBNO", OracleType.VarChar); cmd.Parameters["IN_SNEWSTB_STBNO"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_SNEWSTB_STBNO", OracleType.VarChar); cmd.Parameters["IN_SNEWSTB_STBNO"].Value = ht["STBSRNo"]; } if (ht["ChequeNo"] == null || ht["ChequeNo"] == "") { cmd.Parameters.Add("IN_PISCHEQUE_NO", OracleType.VarChar); cmd.Parameters["IN_PISCHEQUE_NO"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISCHEQUE_NO", OracleType.VarChar); cmd.Parameters["IN_PISCHEQUE_NO"].Value = ht["ChequeNo"]; } if (ht["BankId"] == null || ht["BankId"] == "") { cmd.Parameters.Add("IN_PISBANK_ID", OracleType.Number); cmd.Parameters["IN_PISBANK_ID"].Value = 0; } else { cmd.Parameters.Add("IN_PISBANK_ID", OracleType.Number); cmd.Parameters["IN_PISBANK_ID"].Value = Convert.ToInt64(ht["BankId"]); } if (ht["Branch"] == null || ht["Branch"] == "") { cmd.Parameters.Add("IN_PISBANK_BRANCH", OracleType.VarChar); cmd.Parameters["IN_PISBANK_BRANCH"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISBANK_BRANCH", OracleType.VarChar); cmd.Parameters["IN_PISBANK_BRANCH"].Value = ht["Branch"]; } if (ht["RRNo"] == null || ht["RRNo"] == "") { cmd.Parameters.Add("IN_PISRR_NO", OracleType.VarChar); cmd.Parameters["IN_PISRR_NO"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISRR_NO", OracleType.VarChar); cmd.Parameters["IN_PISRR_NO"].Value = ht["RRNo"]; } if (ht["mposid"] == null || ht["mposid"] == "") { cmd.Parameters.Add("IN_PISMPOS_USERID", OracleType.VarChar); cmd.Parameters["IN_PISMPOS_USERID"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISMPOS_USERID", OracleType.VarChar); cmd.Parameters["IN_PISMPOS_USERID"].Value = ht["mposid"]; } if (ht["AuthCode"] == null || ht["AuthCode"] == "") { cmd.Parameters.Add("IN_PISMPOS_AUTHNUMBER", OracleType.VarChar); cmd.Parameters["IN_PISMPOS_AUTHNUMBER"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISMPOS_AUTHNUMBER", OracleType.VarChar); cmd.Parameters["IN_PISMPOS_AUTHNUMBER"].Value = ht["AuthCode"]; } if (ht["transtype"] == null || ht["transtype"] == "") { cmd.Parameters.Add("IN_PISTRANS_TRANSTYPE", OracleType.VarChar); cmd.Parameters["IN_PISTRANS_TRANSTYPE"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISTRANS_TRANSTYPE", OracleType.VarChar); cmd.Parameters["IN_PISTRANS_TRANSTYPE"].Value = ht["transtype"]; } if (ht["transsubtype"] == null || ht["transsubtype"] == "") { cmd.Parameters.Add("IN_PISTRANS_TRANSSUBTYPE", OracleType.VarChar); cmd.Parameters["IN_PISTRANS_TRANSSUBTYPE"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISTRANS_TRANSSUBTYPE", OracleType.VarChar); cmd.Parameters["IN_PISTRANS_TRANSSUBTYPE"].Value = ht["transsubtype"]; } if (ht["PayMode"] == null || ht["PayMode"] == "") { cmd.Parameters.Add("IN_PISNEWSTB_PAYMODE", OracleType.VarChar); cmd.Parameters["IN_PISNEWSTB_PAYMODE"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISNEWSTB_PAYMODE", OracleType.VarChar); cmd.Parameters["IN_PISNEWSTB_PAYMODE"].Value = ht["PayMode"]; } if (ht["City"] == null || ht["City"] == "") { cmd.Parameters.Add("IN_PISTRANS_CITY", OracleType.Number); cmd.Parameters["IN_PISTRANS_CITY"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISTRANS_CITY", OracleType.Number); cmd.Parameters["IN_PISTRANS_CITY"].Value = ht["City"]; } if (ht["State"] == null || ht["State"] == "") { cmd.Parameters.Add("IN_PISTRANS_STATE", OracleType.Number); cmd.Parameters["IN_PISTRANS_STATE"].Value = 0; } else { cmd.Parameters.Add("IN_PISTRANS_STATE", OracleType.Number); cmd.Parameters["IN_PISTRANS_STATE"].Value = ht["State"]; } if (ht["RateSTB"] == null || ht["RateSTB"] == "") { cmd.Parameters.Add("In_RateSTB", OracleType.Number); cmd.Parameters["In_RateSTB"].Value = 0; } else { cmd.Parameters.Add("In_RateSTB", OracleType.Number); cmd.Parameters["In_RateSTB"].Value = ht["RateSTB"]; } if (ht["DiscountSTB"] == null || ht["DiscountSTB"] == "") { cmd.Parameters.Add("In_DiscountSTB", OracleType.Number); cmd.Parameters["In_DiscountSTB"].Value = 0; } else { cmd.Parameters.Add("In_DiscountSTB", OracleType.Number); cmd.Parameters["In_DiscountSTB"].Value = ht["DiscountSTB"]; } if (ht["NetSTB"] == null || ht["NetSTB"] == "") { cmd.Parameters.Add("In_NetSTB", OracleType.Number); cmd.Parameters["In_NetSTB"].Value = 0; } else { cmd.Parameters.Add("In_NetSTB", OracleType.Number); cmd.Parameters["In_NetSTB"].Value = ht["NetSTB"]; } if (ht["RateLCO"] == null || ht["RateLCO"] == "") { cmd.Parameters.Add("In_RateLCO", OracleType.Number); cmd.Parameters["In_RateLCO"].Value = 0; } else { cmd.Parameters.Add("In_RateLCO", OracleType.Number); cmd.Parameters["In_RateLCO"].Value = ht["RateLCO"]; } if (ht["DiscountLCO"] == null || ht["DiscountLCO"] == "") { cmd.Parameters.Add("In_DiscountLCO", OracleType.Number); cmd.Parameters["In_DiscountLCO"].Value = 0; } else { cmd.Parameters.Add("In_DiscountLCO", OracleType.Number); cmd.Parameters["In_DiscountLCO"].Value = ht["DiscountLCO"]; } if (ht["NetLCO"] == null || ht["NetLCO"] == "") { cmd.Parameters.Add("In_NetLCO", OracleType.Number); cmd.Parameters["In_NetLCO"].Value = 0; } else { cmd.Parameters.Add("In_NetLCO", OracleType.Number); cmd.Parameters["In_NetLCO"].Value = ht["NetLCO"]; } if (ht["TotalNet"] == null || ht["TotalNet"] == "") { cmd.Parameters.Add("In_TotalNet", OracleType.Number); cmd.Parameters["In_TotalNet"].Value = 0; } else { cmd.Parameters.Add("In_TotalNet", OracleType.Number); cmd.Parameters["In_TotalNet"].Value = ht["TotalNet"]; } if (ht["insertBy"] == null || ht["insertBy"] == "") { cmd.Parameters.Add("IN_PISTRANS_INSBY", OracleType.VarChar); cmd.Parameters["IN_PISTRANS_INSBY"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_PISTRANS_INSBY", OracleType.VarChar); cmd.Parameters["IN_PISTRANS_INSBY"].Value = ht["insertBy"]; } if (ht["fauttype"] == null || ht["fauttype"] == "") { cmd.Parameters.Add("in_faulttype", OracleType.VarChar); cmd.Parameters["in_faulttype"].Value = DBNull.Value; } else { cmd.Parameters.Add("in_faulttype", OracleType.VarChar); cmd.Parameters["in_faulttype"].Value = ht["fauttype"]; } cmd.Parameters.Add("in_boxtype", OracleType.VarChar); cmd.Parameters["in_boxtype"].Value = ht["boxtype"]; cmd.Parameters.Add("in_type", OracleType.VarChar); cmd.Parameters["in_type"].Value = ht["type"]; if (ht["MobileNo"] == null || ht["MobileNo"] == "") { cmd.Parameters.Add("in_mobileno", OracleType.VarChar); cmd.Parameters["in_mobileno"].Value = DBNull.Value; } else { cmd.Parameters.Add("in_mobileno", OracleType.VarChar); cmd.Parameters["in_mobileno"].Value = ht["MobileNo"]; } cmd.Parameters.Add("in_ChqDate", OracleType.DateTime); cmd.Parameters["in_ChqDate"].Value = ht["ChequeDate"]; if (ht["AccessString"] == null || ht["AccessString"] == "") { cmd.Parameters.Add("IN_Accessstr", OracleType.VarChar); cmd.Parameters["IN_Accessstr"].Value = DBNull.Value; } else { cmd.Parameters.Add("IN_Accessstr", OracleType.VarChar); cmd.Parameters["IN_Accessstr"].Value = ht["AccessString"]; } cmd.Parameters.Add("Out_ErrorCode", OracleType.Number); cmd.Parameters["Out_ErrorCode"].Direction = ParameterDirection.Output; cmd.Parameters.Add("Out_ErrorMsg", OracleType.VarChar, 1000); cmd.Parameters["Out_ErrorMsg"].Direction = ParameterDirection.Output; ConObj.Open(); cmd.ExecuteNonQuery(); int exeResult = Convert.ToInt32(cmd.Parameters["Out_ErrorCode"].Value); string ExeResultMsg = Convert.ToString(cmd.Parameters["out_ErrorMsg"].Value); return(exeResult + "$" + ExeResultMsg); ConObj.Dispose(); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(ht["UserName"].ToString(), ex.Message.ToString(), "frmNewSTB.cs"); return("ex_occured"); } finally { ConObj.Close(); ConObj.Dispose(); } }