public DataTable GetDetails(Hashtable HT, string username)
{
try
{
string Type = HT["Type"].ToString();
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "";
if (Type == "VC")
{
StrQry = "select * from view_lcopre_InvunusedVC where LcoCode='" + username + "'";
}
else
{
StrQry = "select * from view_lcopre_Invunusedstb where LcoCode='" + username + "'";
}
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 getpartyledDet(Hashtable htAddPlanParams)
{
try
{
string city = htAddPlanParams["city"].ToString();
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = " SELECT b.var_partled_lcocode,b.var_partled_lconame,b.num_partled_openinbal,(to_char(b.dat_partled_date,'dd-Mon-yyyy hh12:mi:ss AM'))partyleddate " +
" FROM aoup_lcopre_lco_partyled_mst b, aoup_lcopre_lco_det c " +
" WHERE dat_partled_date =(SELECT MIN (dat_partled_date) " +
" FROM aoup_lcopre_lco_partyled_mst a " +
" WHERE a.var_partled_lcocode = b.var_partled_lcocode) " +
" AND c.var_lcomst_code = b.var_partled_lcocode ";
if (city.Trim() != "All")
{
StrQry += " and c.num_lcomst_cityid='" + city.ToString() + "'";
}
StrQry += " order by b.dat_partled_date";
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb("admin_ho", ex.Message.ToString(), "Cls_Data_rptpartyledopenbal-getpartyledDet");
return(null);
}
}
public DataTable GetExcelData(string username, string upload_id, int status)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = " select * from view_lcopre_bulk_master a " +
" where a.upload_id = '" + upload_id + "' ";
if (status == 1)
{
StrQry += " and a.status = 'Success' ";
}
else if (status == 2)
{
StrQry += " and a.status = 'Failed' ";
}
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_rptBulkUpload-GetExcelData");
return(null);
}
}
public DataTable GetTransationsLastFDets(string whereClauseStr2, string username2)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
//StrQry = "SELECT a.transid, a.receiptno, a.custid, a.vc, a.custname, a.custaddr, a.planid, ";
//StrQry += " a.plnname, a.plntyp, a.amtdd, a.lcoprice, a.expdt, a.payterm, ";
//StrQry += " a.bal, a.companycode, a.flag, a.insby, a.dt, a.tdt, a.uname, ";
//StrQry += " a.userowner, a.usrid, a.lcocode, a.lconame, a.jvname, ";
//StrQry += " a.erplco_ac, a.distname, a.subdist, a.city, a.state,a.custprice,a.amtdd,a.flag ";
//StrQry += " FROM view_lcoall_last_trans_det a ";
//StrQry += " where " + whereClauseStr2;
StrQry = "SELECT a.transid, a.receiptno, a.custid, a.vc, a.custname, a.planid,a.addr, ";
StrQry += " a.plnname, a.plntyp, a.amtdd, a.custprice, a.expdt, a.payterm, ";
StrQry += " a.bal, a.companycode, a.flag, a.insby, a.dt, a.tdt, a.uname, ";
StrQry += " a.userowner, a.usrid, a.lcocode, a.lconame, a.jvname, ";
StrQry += " a.erplco_ac, a.distname, a.subdist, a.city, a.state, a.reason ";
StrQry += " FROM view_lcopre_user_trans_det a ";
StrQry += " where " + whereClauseStr2;
StrQry += " and rownum <=5 ";
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username2, ex.Message.ToString(), "Cls_Data_RptLcoAll-GetTransationsLastFDets");
return(null);
}
}
public DataTable STBInvConfList(string username)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
//StrQry = " select receiptno, subtypex, transtype, paymentmode, totalnet amount, lcodiscount discount, xtype, TRANSSUBTYPE1, SCHEME, BOXTYPE, dat_pistrans_insdate insdate,MAKEMODEL ,SKYWORTH ";
//StrQry += "from view_warehouseauth_list ";
//StrQry += "inner join aoup_lcopre_pis_spnewstb_det on num_pisnewstb_id = pistrans_id and ";
//StrQry += "var_pisnewstb_processed is null where LCOCODE='" + username + "' and STBPENDINGCOUNT< stbcount and vcPENDINGCOUNT< stbcount ";
//StrQry += "group by receiptno, subtypex, transtype, paymentmode, totalnet , lcodiscount , xtype, TRANSSUBTYPE1, SCHEME, BOXTYPE, dat_pistrans_insdate,MAKEMODEL,SKYWORTH ";
//StrQry += "order by dat_pistrans_insdate desc ";
StrQry = " select receiptno, subtypex, transtype, paymentmode, totalnet amount, lcodiscount discount, xtype, TRANSSUBTYPE1, SCHEME, BOXTYPE, dat_pistrans_insdate insdate,MAKEMODEL ,SKYWORTH ";
StrQry += " from view_warehouseauth_list ";
StrQry += " inner join aoup_lcopre_pis_spnewstb_det on num_pisnewstb_id = pistrans_id and ";
StrQry += " var_pisnewstb_processed is null where STBPENDINGCOUNT< stbcount and vcPENDINGCOUNT< stbcount and pdcflag='N'";
StrQry += " group by receiptno, subtypex, transtype, paymentmode, totalnet , lcodiscount , xtype, TRANSSUBTYPE1, SCHEME, BOXTYPE, dat_pistrans_insdate,MAKEMODEL,SKYWORTH ";
StrQry += " union ";
StrQry += " select receiptno, subtypex, transtype, paymentmode, totalnet amount, lcodiscount discount, xtype, TRANSSUBTYPE1, SCHEME, BOXTYPE, dat_pistrans_insdate insdate,MAKEMODEL ,SKYWORTH ";
StrQry += " from view_warehouseauth_list ";
StrQry += " inner join aoup_lcopre_pis_spnewvc_det on num_pisnewvc_id = pistrans_id and ";
StrQry += " var_pisnewvc_processed is null where vcPENDINGCOUNT< stbcount and pdcflag='N' ";
StrQry += " group by receiptno, subtypex, transtype, paymentmode, totalnet , lcodiscount , xtype, TRANSSUBTYPE1, SCHEME, BOXTYPE, dat_pistrans_insdate,MAKEMODEL,SKYWORTH order by insdate desc ";
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_STBInventory_Conformation.cs");
return(null);
}
}
public void GetAccepGST(string LCOCODE, out string lconame)
{
lconame = "";
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
try
{
string str = "";
str = " select var_gst_lconame from aoup_lcopre_gst_acceptance left join aoup_user_def on num_user_operid=var_gst_operid where var_user_username='******'";
OracleCommand cmd = new OracleCommand(str, conObj);
conObj.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
lconame = dr["var_gst_lconame"].ToString();
}
conObj.Close();
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(LCOCODE, ex.Message.ToString(), "Cls_Data_Auth-GetAccepGST");
}
finally
{
conObj.Close();
conObj.Dispose();
}
}
//----Added By RP on 28.09.2017
public string LCOContactNo(string UserName)
{
try
{
string LCOContactNo = "";
string str = " select to_char(num_lcomst_mobileno) lcomobno from aoup_lcopre_lco_det a inner join aoup_user_def b on num_user_operid=a.num_lcomst_operid where b.var_user_username='******'";
string strConn = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection oraConn = new OracleConnection(strConn);
OracleCommand cmd = new OracleCommand(str, oraConn);
oraConn.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LCOContactNo = dr["lcomobno"].ToString();
}
oraConn.Close();
return(LCOContactNo);
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(UserName, ex.Message.ToString(), "Cls_Data_Auth-SendAndroidUrl");
return(ex.Message.ToString());
}
}
public DataTable Getdata(string Uploadid, string Type, string username)
{
try
{
Cls_Helper obj = new Cls_Helper();
string StrQry = " select var_inventval_upload_id UploadID,var_inventval_orderno orderno,var_inventval_lcocode lcocode,var_inventval_deviceid deviceid,var_inventval_boxtype boxtype, ";
StrQry += "var_inventval_type Type,case when var_inventval_apistatus='0' then 'Success' when var_inventval_apistatus='1' then 'Fail' ";
StrQry += "when var_inventval_apistatus is null then 'Pending' end APIStatus,var_inventval_apismsg apimsg from aoup_lcopre_pis_invent_raw ";
StrQry += " where var_inventval_valtype='LM' and var_inventval_upload_id='" + Uploadid + "' and var_inventval_insby='" + username + "' ";
if (Type != "All" && Type != "")
{
StrQry += " and var_inventval_apistatus='" + Type + "'";
}
else if (Type == "All")
{
}
else if (Type == "")
{
StrQry += " and var_inventval_apistatus is null ";
}
return(obj.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_Warehouse.cs");
return(null);
}
}
public DataTable GetLastFiveTransaction(string whereClauseStr, string username, string category, string operid)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "SELECT a.transid, a.receiptno, a.custid, a.vc, a.custname, a.planid, ";
StrQry += " a.plnname, a.plntyp, a.amtdd, a.custprice, a.expdt, a.payterm, ";
StrQry += " a.bal, a.companycode, a.flag, a.insby, a.dt, a.tdt, a.uname, ";
StrQry += " a.userowner, a.usrid, a.lcocode, a.lconame, a.jvname, ";
StrQry += " a.erplco_ac, a.distname, a.subdist, a.city, a.state, a.reason ";
StrQry += " FROM view_lcopre_user_trans_det a ";
if (category == "3" || category == "11")
{
StrQry += " where a.operid='" + operid + "' ";
}
else
{
StrQry += " where " + whereClauseStr;
}
StrQry += " and rownum <=5 ";
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_RptLastFiveGridBind.cs");
return(null);
}
}
//--- Plan Share Procedure Calling here
public string bulkPlanUpload(string username)
{
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection ConObj = new OracleConnection(ConStr);
try
{
ConObj.Open();
OracleCommand Cmd = new OracleCommand("aoup_lcopre_lcoshare_ins", ConObj);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("out_errcode", OracleType.Number);
Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output;
Cmd.ExecuteNonQuery();
ConObj.Close();
string exeCode = Convert.ToString(Cmd.Parameters["out_errcode"].Value);
return(exeCode);
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_TransBulk_Discount-bulkUploadMst");
return("-300#" + ex.Message.ToString());
}
finally
{
ConObj.Close();
ConObj.Dispose();
}
}
public DataTable getExpiryData(string username, string OpID, string CatId)
{
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
DataTable dt = new DataTable();
try
{
conObj.Open();
string query = " select stake_holder,title,priority, sum(a.col1)as col1,sum(a.col2)as col2,sum(a.col3)as col3,sum(a.col4)as col4,sum(a.col5)as col5,sum(a.col6)as col6, " +
" sum(a.col7)as col7,sum(a.col8)as col8,sum(a.col9)as col9, sum(a.col10)as col10,sum(a.col11)as col11,sum(a.col12)as col12, sum(a.col13)as col13,sum(a.col14)as col14, " +
" sum(a.col15)as col15,sum(a.col16)as col16,sum(a.col17)as col17,sum(a.col18)as col18 FROM (SELECT case when 10='" + CatId + "' then ho_name when 2='" + CatId + "' then mso_name " +
" when 5='" + CatId + "' then dist_name else lconame end as stake_holder,a.title, a.priority, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, " +
" col11, col12, col13, col14, col15,col16,col17,col18 FROM view_lcopre_dshbrd_exp a where case when 10='" + CatId + "' then ho_id " +
" when 2='" + CatId + "' then mso when 5='" + CatId + "' then distri else lcoid end ='" + OpID + "'" +
" ) a group by stake_holder,title,priority order by stake_holder,priority asc ,title asc ";
Cls_Helper obDt = new Cls_Helper();
dt = obDt.GetDataTable(query);
return(dt);
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "cls_Data_frmdashboard-GetExpiryDetails");
return(dt);
}
finally
{
conObj.Close();
conObj.Dispose();
}
}
public DataTable getMSOuserdetails(string username, string category, string operid)
{
try
{
string StrQry = "SELECT a.userid, a.username, a.userowner, a.fname, a.mname, ";
StrQry += " a.lname, a.addr, a.code, a.brmpoid, a.ststeid, a.cityid, a.email, ";
StrQry += " a.mobno, a.compcode, a.accno, a.insby, a.insdt, a.flag,a.OPER_ID,a.PARENTID,a.HO_OPER_ID ";
StrQry += " FROM view_msopre_user_det a ";
if (category == "2")
{
StrQry += " where a.PARENTID='" + operid + "'";
}
else if (category == "3")
{
StrQry += " where a.OPER_ID='" + operid + "'";
}
else if (category == "10")
{
StrQry += " where a.HO_OPER_ID='" + operid + "'";
}
Cls_Helper ObjHelper = new Cls_Helper();
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "cls_data_rptUserdetailsMSOwise.cs");
return(null);
}
}
public DataTable getLCODataLco(string whereClauseStr, string username, string category)
{
try
{
//DataTable dtLCO = new DataTable("LCO");
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = " SELECT a.num_usermst_id, a.lcoid, a.var_usermst_username, ";
StrQry += " a.var_usermst_name, a.var_usermst_firstname, ";
StrQry += " a.var_usermst_middlename, a.var_usermst_lastname, ";
StrQry += " a.var_usermst_address, a.var_usermst_code, a.var_usermst_brmpoid, ";
StrQry += " a.num_usermst_stateid, a.num_usermst_cityid, a.var_usermst_email, ";
StrQry += " a.num_usermst_mobileno, a.var_lcomst_companycode, ";
StrQry += " a.var_usermst_accno, a.var_usermst_insby, a.var_usermst_insdt, ";
StrQry += " a.var_usermst_flag, a.lconame, a.lcocode,a.jvno,a.directno ";
StrQry += " FROM view_prelco_lco_uddet a ";
StrQry += " where " + whereClauseStr;
/*dtLCO = objHelper.GetDataTable(strLCOQry);
* DataSet dsLCOData = new DataSet();
* dsLCOData.Tables.Add(dtLCO);
* return dsLCOData;*/
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstLCOPreUDefine.cs-getUserData");
return(null);
}
}
public DataTable getPlanData()
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = " SELECT a.var_plan_name, a.var_plan_plantype, a.var_plan_planpoid, " +
" a.var_plan_dealpoid, a.var_plan_productpoid, " +
" a.num_plan_custprice, a.num_plan_lcoprice, a.var_city_name, a.AREA " +
" FROM view_lcopre_plan_det a ";
/*
* " SELECT a.var_plan_name, (case when a.var_plan_plantype='B' then 'Basic' " +
* " when a.var_plan_plantype='AL' then 'A-La-Carte' when a.var_plan_plantype='AD' then 'Addon' end) var_plan_plantype," +
* " a.var_plan_planpoid, a.var_plan_dealpoid, a.var_plan_productpoid, " +
* " a.num_plan_custprice, a.num_plan_lcoprice,b.var_city_name " +
* " FROM aoup_lcopre_plan_def a,aoup_lcopre_city_def b " +
* " WHERE a.num_plan_cityid=b.num_city_id " +
* " ORDER by b.var_city_name,a.var_plan_name";
*/
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb("admin_ho", ex.Message.ToString(), "Cls_Data_rptPlanDetails-getPlanData");
return(null);
}
}
public DataTable getLCOData(string whereClauseStr, string username)
{
try
{
//DataTable dtLCO = new DataTable("LCO");
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "SELECT a.mst_id, a.lcoid, a.lconame, a.fname, a.mname, a.lname, a.addr,";
StrQry += " a.lcocode, a.stateid, a.cityid, a.email, a.mobileno, a.jvno, ";
StrQry += " a.directno, a.brmpoid, a.company, a.distname, a.subdist, ";
StrQry += " a.companycode, a.insby, a.insdt, a.insdt1, a.pin, a.operid, ";
StrQry += " a.opercategory, a.parentid, a.distid, a.state, a.city ";
StrQry += " FROM view_prelco_lco_det a ";
StrQry += " where " + whereClauseStr;
/*dtLCO = objHelper.GetDataTable(strLCOQry);
* DataSet dsLCOData = new DataSet();
* dsLCOData.Tables.Add(dtLCO);
* return dsLCOData;*/
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Data_MstLCOUpdateDetails.cs-getUserData");
return(null);
}
}
public DataTable GetTransationsReversDets(string whereClauseStr4, string username4)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "SELECT transid, voucherno, ";
StrQry += " amount, reasonname, lcopayremark, ";
StrQry += " companycode, insby, a.date1, ";
StrQry += " insdt, lcocode,";
StrQry += " chequebouncedt, a.operid, a.parentid,";
StrQry += " a.distid FROM view_lcoovervw_revrs_det a ";
StrQry += " where " + whereClauseStr4;
StrQry += " and rownum <=5 ";
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username4, ex.Message.ToString(), "Cls_Data_RptLcoAll-GetTransationsReversDets");
return(null);
}
}
public DataTable GetPaymentRev(string whereClauseStr, string username, string operid, string cat)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "SELECT a.num_lcopay_transid, a.var_lcopay_voucherno, ";
StrQry += " a.num_lcopay_amount, a.var_reason_name, a.var_lcopay_remark, ";
StrQry += " a.var_lcopay_companycode, a.var_lcopay_insby, a.date1, ";
StrQry += " a.dat_lcopay_insdt, a.var_lcopay_lcocode, a.var_lcomst_name, ";
StrQry += " a.dat_lcopay_chequebouncedt, a.num_oper_id, a.num_oper_parentid, ";
StrQry += " a.num_oper_distid, a.hoid, a.cityname, a.statename, ";
StrQry += " a.companyname, a.distributor, a.subdistributor, a.payment_dt, a.payment_mode, ";
StrQry += " a.bank, a.branch, a.cashier, a.cheque_no ";
StrQry += " FROM view_lco_payement_revoke a ";
StrQry += " where " + whereClauseStr;
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_RptLCOPayRev.cs");
return(null);
}
}
//---- Added by RP on 12.12.2017
public void GetInventryBalance(string UserName, out string Balance, out string InBalance)
{
Balance = "";
InBalance = "";
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
try
{
string str = "";
str = " select nvl(num_user_curcrlimit,0) Bal, nvl(num_lcomst_invbal,0) invbal from aoup_user_def where var_user_username='******' ";
OracleCommand cmd = new OracleCommand(str, conObj);
conObj.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Balance = dr["bal"].ToString();
InBalance = dr["invbal"].ToString();
}
conObj.Close();
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(UserName, ex.Message.ToString(), "Cls_Data_TransHome-GetInventryBalance");
}
finally
{
conObj.Close();
conObj.Dispose();
}
}
public DataTable getOverviewdata(string username, string OpID, string CatId)
{
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
DataTable dtOverview = new DataTable();
try
{
conObj.Open();
string query = " select stake_holder,title,priority, sum(col1)as col1,sum(col2)as col2,sum(col3)as col3 FROM (SELECT case when 10='" + CatId + "' then " +
" ho_name when 2='" + CatId + "' then mso_name when 5='" + CatId + "' then dist_name else lconame end as stake_holder,a.title, a.priority, col1,col2,col3 " +
" FROM view_lcopre_dshbrd_overview a where case when 10='" + CatId + "' then ho_id when 2='" + CatId + "' then mso when 5='" + CatId + "' then " +
" distri else lcoid end ='" + OpID + "') group by stake_holder,title,priority order by priority ";
Cls_Helper obDt = new Cls_Helper();
dtOverview = obDt.GetDataTable(query);
return(dtOverview);
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "cls_Data_frmdashboard-getOverview");
return(dtOverview);
}
finally
{
conObj.Close();
conObj.Dispose();
}
}
public DataTable GetTransationsU(string whereClauseStr1, string username1, string TableName)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "SELECT lconame, uname, usrid, userowner, sum(amt)amt, sum(cnt)cnt,sum(amtdd) amtdd from " +
"( select b.num_lcomst_operid lcoid, b.var_lcomst_code lcocode, b.var_lcomst_name lconame ,c.num_usermst_id usrid, c.var_usermst_username uname, c.var_usermst_name userowner," +
" trunc(a.dat_custpay_transdt) dt,count(a.num_custpay_transid) cnt, sum(nvl(a.num_custpay_balance,0))amt,sum(nvl(a.num_custpay_lcoprice,0))amtdd" +
" , xx.num_oper_id OPERID, xx.num_oper_distid distid, xx.num_oper_parentid PARENTID, yy.num_oper_parentid hoid,a.var_custpay_vcid VCMAC_Id,a.var_custpay_custid Account_No,xx.num_oper_clust_id clustid," +
" (case when a.var_custpay_plantype = 'AD' then 'Addon' when a.var_custpay_plantype = 'AL' then 'A-La-Carte'" +
" when a.var_custpay_plantype = 'B' then 'Basic' else a.var_custpay_plantype end) PlanType,(CASE WHEN a.var_custpay_flag = 'R' THEN 'Renewal' WHEN a.var_custpay_flag = 'A' THEN 'Activation' WHEN a.var_custpay_flag = 'C' THEN 'Cancellation'" +
" WHEN a.var_custpay_flag = 'RR' THEN 'Failure Refund' WHEN a.var_custpay_flag = 'AR' THEN 'Failure Refund' WHEN a.var_custpay_flag = 'CR' THEN 'Failure Refund'" +
" WHEN a.var_custpay_flag = 'CHR' THEN 'Failure Refund' WHEN a.var_custpay_flag = 'CH' THEN 'Cancellation' END) planflag,a.var_custpay_payterm payterm" +
" from " + TableName + " a, aoup_lcopre_lco_det b, aoup_lcopre_user_det c, aoup_operator_def xx,aoup_operator_def yy where a.var_custpay_insby=b.num_lcomst_operid" +
" and a.var_custpay_user=c.var_usermst_username and c.num_usermst_operid=xx.num_oper_id and yy.num_oper_id=xx.num_oper_parentid" +
" group by b.num_lcomst_operid , b.var_lcomst_code , b.var_lcomst_name ,c.num_usermst_id , c.var_usermst_username , c.var_usermst_name ,a.var_custpay_vcid,a.var_custpay_custid," +
" trunc(a.dat_custpay_transdt), xx.num_oper_id , xx.num_oper_distid , xx.num_oper_parentid, yy.num_oper_parentid,xx.num_oper_clust_id,a.var_custpay_plantype,a.var_custpay_flag,a.var_custpay_payterm )" +
" where " + whereClauseStr1 +
" group by lconame, uname, usrid, userowner ";
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username1, ex.Message.ToString(), "Cls_Data_RptAddPlan.cs");
return(null);
}
}
public DataTable GetPlans(string from, string to, string username, string operid, string catid)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "SELECT distinct a.planname" +
" FROM view_lcopre_expiry a " +
" where a.enddate >='" + from + "' " +
" and a.enddate <='" + to + "'";
if (catid == "3" || catid == "11")
{
StrQry += " and a.operid = '" + operid + "'";
}
else if (catid == "10")
{
StrQry += " and a.hoid = '" + operid + "'";
}
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_rptExpiry.cs-GetPlans");
return(null);
}
}
public string UpdateAmount(string sp, Hashtable HT)
{
string result = "";
try
{
string strcon = ConfigurationSettings.AppSettings["ConString"].ToString();
OracleConnection con = new OracleConnection(strcon);
OracleCommand cmd = new OracleCommand(sp, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("in_username", OracleType.VarChar);
cmd.Parameters.Add("in_lcocode", OracleType.VarChar);
cmd.Parameters.Add("in_Amount", OracleType.Number);
cmd.Parameters.Add("in_reason", OracleType.VarChar);
cmd.Parameters.Add("out_errcode", OracleType.Number, 100);
cmd.Parameters["in_username"].Value = HT["LCOcode"].ToString();
cmd.Parameters["in_lcocode"].Value = HT["LCOcode"].ToString();
cmd.Parameters["in_Amount"].Value = Convert.ToInt32(HT["Amount"]);
cmd.Parameters["in_reason"].Value = HT["Remark"].ToString();
cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
int exeResult = Convert.ToInt32(cmd.Parameters["out_errcode"].Value);
con.Dispose();
return(result = Convert.ToString(exeResult));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(HT["LCOcode"].ToString(), ex.Message.ToString(), "Cls_Data_TransInvAmountMove.cs");
return("ex_occured");
}
}
public void Get_Faulty_Swap(string whereClauseStr, string username, out string OutStatus)
{
OutStatus = "";
try
{
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "select * from reports.stb_dump_final@caslive_new ";
StrQry += " where " + whereClauseStr;
OracleCommand cmd = new OracleCommand(StrQry, conObj);
conObj.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
OutStatus = dr["nds_no"].ToString();
}
conObj.Close();
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_Faulty_Swap.cs");
}
}
public void GetRequTomor(string LCO_Code, out string RequTomor)
{
RequTomor = "";
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
try
{
string str = "";
str = " select nvl(SUM(num_plan_lcoprice),0) TomorRequed from REPORTS.hwcas_brm_cust_master@CASLIVE_new a,view_lcopre_lcoplan_def b ";
str += " where b.var_plan_name=a.productname and a.lco_code = b.var_lcomst_code";
str += " and enddate = trunc(sysdate + 1) and status = 'ACTIVE' and lco_code = '" + LCO_Code + "'";
OracleCommand cmd = new OracleCommand(str, conObj);
conObj.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
RequTomor = dr["TomorRequed"].ToString();
}
conObj.Close();
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(LCO_Code, ex.Message.ToString(), "Cls_Data_TransHwayBulkOperation.cs-GetRequTomor");
}
finally
{
conObj.Close();
conObj.Dispose();
}
}
//---- Added By RP on 28.06.2017
public void GetGSTNo(string LCOCODE, out string GSTNO)
{
GSTNO = "";
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
try
{
string str = "";
// str = " select gstin from reports.lco_gstn_master@caslive_new where account_no= '" + LCOCODE + "'";
str = "select * from reports.lco_gstn_master@caslive_new where account_no in(";
str += "select var_usermst_username from aoup_lcopre_user_det where num_usermst_operid='" + LCOCODE + "' and var_usermst_flag='M')";
OracleCommand cmd = new OracleCommand(str, conObj);
conObj.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
GSTNO = dr["gstin"].ToString();
}
conObj.Close();
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(LCOCODE, ex.Message.ToString(), "Cls_Data_Auth-GetGSTNo");
}
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 lco = htAddPlanParams["lco"].ToString();
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = " select a.var_sms_vcid,a.num_sms_clcontact,a.var_sms_message,a.var_sms_status,a.var_sms_username,to_char(a.date_sms_dt, 'dd-Mon-yyyy') date_sms_dt " +
" FROM view_lcopre_notif_sms_log a " +
" where a.dt >='" + from + "' " +
" and a.dt <='" + to + "' " +
// " and upper(a.var_sms_username) =upper('" + username + "') ";
" and a.var_sms_username='******'";
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_rptSMSDelivery.cs-GetDetails");
return(null);
}
}
public string GARstatuschange(Hashtable ht)
{
OracleConnection ConObj = new OracleConnection(ConfigurationSettings.AppSettings["ConString"].ToString().Trim());
try
{
ConObj.Open();
OracleCommand Cmd = new OracleCommand("aoup_lco_gauto_renewal", ConObj);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("in_username", OracleType.VarChar, 100);
Cmd.Parameters["in_username"].Value = ht["User"];
Cmd.Parameters.Add("in_flag", OracleType.VarChar, 100);
Cmd.Parameters["in_flag"].Value = ht["flag"];
Cmd.Parameters.Add("in_lcocode", OracleType.VarChar, 50);
Cmd.Parameters["in_lcocode"].Value = ht["lcocode"];
Cmd.Parameters.Add("out_errcode", OracleType.Number);
Cmd.Parameters["out_errcode"].Direction = ParameterDirection.Output;
Cmd.Parameters.Add("out_data", OracleType.VarChar, 2000);
Cmd.Parameters["out_data"].Direction = ParameterDirection.Output;
Cmd.ExecuteNonQuery();
ConObj.Close();
int exeResult = Convert.ToInt32(Cmd.Parameters["out_errcode"].Value);
string Str;
if (exeResult == 9999)
{
Str = "9999";
Str += ",Global Auto Renew Status Changed successfully...";
}
else
{
Str = exeResult.ToString();
Str += "," + Convert.ToString(Cmd.Parameters["out_errortext"].Value);
}
ConObj.Dispose();
return(Str);
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(ht["User"].ToString(), ex.Message.ToString(), "Cls_Data_TransHwayGlobalAutoRenew.cs");
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 plan_name = "All";
string accvctxt = "";
string accvc = "";
if (htAddPlanParams["accvctxt"] != null)
{ //in case of expiry report, this will be null
accvctxt = htAddPlanParams["accvctxt"].ToString();
accvc = htAddPlanParams["accvc"].ToString();
}
;
if (htAddPlanParams["plan_name"] != null)
{
}
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.cityname " +
" FROM view_lcopre_expired_rpt_new a " +
" where a.enddate >='" + from + "'" +
" and a.enddate <='" + to + "'";
if (plan_name.Trim() != "All")
{
StrQry += " and trim(a.planname) = trim('" + plan_name + "')";
}
if (accvctxt != "")
{
if (accvc == "ACC")
{
StrQry += " and ACCOUNT_NO='" + accvctxt.ToString() + "'";
}
else if (accvc == "VC")
{
StrQry += " and vc='" + accvctxt.ToString() + "'";
}
}
if (catid == "3" || catid == "11")
{
StrQry += " and a.LCO_CODE = '" + operid + "'";
}
else if (catid == "10")
{
StrQry += " and a.hoid = '" + operid + "'";
}
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_rptExpired.cs-GetDetails");
return(null);
}
}
/*-------- RP on 07.09.2017 -------- */
public string[] BindSchemDetails(string SchemeID, string username)
{
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
try
{
string str = "";
str += " SELECT num_scheme_id, var_scheme_name SchemeName, var_scheme_description Scheme_Description,case when termination_allowed ='Y' then 'Yes' ";
str += " when termination_allowed='N' then 'No' end termination_allowed,penalty_on_foreclosure penalty, ";
str += " case when plan_change_allowed ='Y' then 'Yes' when plan_change_allowed='N' then 'No' end plan_change_allowed, ";
str += " case when activation_allowed ='Y' then 'Yes' when activation_allowed='N' then 'No' end activation_allowed,";
str += " case when num_subscription_payterm ='1' then '1 Month' when num_subscription_payterm='3' then '3 Month' when num_subscription_payterm='6' then '6 Month' ";
str += " when num_subscription_payterm='12' then '12 Month' end num_subscription_payterm, var_subscription_plan,";
str += " num_subscription_lcorate LcoRate, subscription_discount_lcorate LcoDiscount, subscription_net_lcorate LcoNet, max_stb_quanity_allowed StbCount, var_stb_status StbStatus,";
str += " var_user_boxtype BoxType, var_stb_makemodel Model, num_scheme_value StbRate,num_stb_discount StbDiscount, num_stb_netamount stbNet FROM aoup_lcopre_scheme_master";
str += " where num_scheme_id='" + SchemeID + "'";
OracleCommand cmd = new OracleCommand(str, conObj);
conObj.Open();
List <string> Operators = new List <string>();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Operators.Add(dr["SchemeName"].ToString());
Operators.Add(dr["Scheme_Description"].ToString());
Operators.Add(dr["termination_allowed"].ToString());
Operators.Add(dr["penalty"].ToString());
Operators.Add(dr["plan_change_allowed"].ToString());
Operators.Add(dr["activation_allowed"].ToString());
Operators.Add(dr["num_subscription_payterm"].ToString());
Operators.Add(dr["var_subscription_plan"].ToString());
Operators.Add(dr["lcorate"].ToString());
Operators.Add(dr["lcodiscount"].ToString());
Operators.Add(dr["lconet"].ToString());
Operators.Add(dr["stbcount"].ToString());
Operators.Add(dr["stbstatus"].ToString());
Operators.Add(dr["boxtype"].ToString());
Operators.Add(dr["model"].ToString());
Operators.Add(dr["stbrate"].ToString());
Operators.Add(dr["stbdiscount"].ToString());
Operators.Add(dr["stbnet"].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 bulkValidate(string username, string upload_id, string cust_data, string service_data, string action, string plan_name, string lco_code)
{
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection ConObj = new OracleConnection(ConStr);
try
{
ConObj.Open();
OracleCommand Cmd = new OracleCommand("aoup_lcopre_bulk_validation", ConObj);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("in_username", OracleType.VarChar, 50);
Cmd.Parameters["in_username"].Value = username;
Cmd.Parameters.Add("in_useruniqueid", OracleType.VarChar, 100);
Cmd.Parameters["in_useruniqueid"].Value = upload_id;
Cmd.Parameters.Add("in_custstr", OracleType.VarChar, 4000);
Cmd.Parameters["in_custstr"].Value = cust_data;
Cmd.Parameters.Add("in_planstr", OracleType.VarChar, 5000);
Cmd.Parameters["in_planstr"].Value = service_data;
Cmd.Parameters.Add("in_action", OracleType.VarChar, 1);
Cmd.Parameters["in_action"].Value = action;
Cmd.Parameters.Add("in_planname", OracleType.VarChar, 1000);
Cmd.Parameters["in_planname"].Value = plan_name;
//Cmd.Parameters.Add("in_lcocode", OracleType.VarChar, 1000);
//Cmd.Parameters["in_lcocode"].Value = 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 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(username, ex.Message.ToString(), "Cls_Data_TransHwayBulkOperation-bulkValidate");
return("-300#" + ex.Message.ToString());
}
finally
{
ConObj.Close();
ConObj.Dispose();
}
}
