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