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