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 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 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 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 void bndJVDDl() { string _getPlan = "select DISTINCT var_comp_company,var_comp_company from aoup_lcopre_company_det "; Cls_Helper obp = new Cls_Helper(); DataTable dtp = obp.GetDataTable(_getPlan); if (dtp == null) { return; } if (dtp.Rows.Count == 0) { return; } if (dtp.Rows.Count > 0) { ddlJV.DataSource = dtp; ddlJV.DataTextField = "var_comp_company"; ddlJV.DataValueField = "var_comp_company"; ddlJV.DataBind(); ddlJV.Items.Insert(0, new ListItem("All", "0")); } else { ddlJV.Items.Clear(); ddlJV.Items.Insert(0, new ListItem("All", "0")); ddlJV.DataBind(); } }
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 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 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 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 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 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 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 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 Hashtable getReport(string username, Hashtable ht) { string whereString = ""; string from = ht["from"].ToString(); string to = ht["to"].ToString(); string searchParamStr = ""; if (from != null && from != "") { whereString += " trunc(dt) >= '" + from + "' "; searchParamStr += " <b>Transaction From : </b> " + from; } if (to != null && to != "") { whereString += " and trunc(dt) <= '" + to + "' "; searchParamStr += " <b>Transaction To : </b> " + to; } whereString += " and a.owner='" + username + "' "; string _getdata = "select * from view_rptcrf_details a where " + whereString + " "; Cls_Helper obj = new Cls_Helper(); Hashtable htResponse = new Hashtable(); htResponse.Add("data", obj.GetDataTable(_getdata)); htResponse.Add("ParamStr", searchParamStr); return(htResponse); }
protected void loadbasicplan() { string username = Session["username"].ToString(); string where_str = ""; DataSet ds; if (chkaddmap.Checked) { where_str = " where num_plan_cityid='" + ddladdcity.SelectedValue.ToString() + "' and var_plan_plantype='B'"; ds = Cls_Helper.Comboupdate(" aoup_lcopre_lcoplan_def " + where_str + " group by num_plan_plantypeid,var_plan_name ORDER BY var_plan_name", "num_plan_plantypeid", "var_plan_name"); ddladdplan.DataSource = ds; ddladdplan.DataTextField = "var_plan_name"; ddladdplan.DataValueField = "num_plan_plantypeid"; ddladdplan.DataBind(); ddladdplan.Dispose(); } else { where_str = " where num_plan_cityid='" + ddladdcity.SelectedValue.ToString() + "' and var_plan_plantype in('AD','GAD','RAD')"; ds = Cls_Helper.Comboupdate(" aoup_lcopre_lcoplan_def " + where_str + " group by var_plan_name ORDER BY var_plan_name", "0", "var_plan_name"); ddladdplan.DataSource = ds; ddladdplan.DataTextField = "var_plan_name"; ddladdplan.DataValueField = "var_plan_name"; ddladdplan.DataBind(); ddladdplan.Dispose(); } }
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); } }
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 void BindGrid() { Int32 DepId = Convert.ToInt32(Session["UserDepid"]); String Query = " select cmpno ,deptnm ,depid ,source,authby,cmptype, cmpsubtype, custnm, custno, cmpdesc,cmpstatus,srvst,TO_CHAR(regdt,'dd-MM-yyyy') regdt, assgnuser, userremark, remarkdate,CHECKEDSTATUS,LCOCODE,companyname,callername,callerno,flag,Alternateno from crm.mview_rpt_complaint_details "; Query += " where checkedstatus='Y' and trunc(regdt) >= TO_DATE('" + txtFrom.Text + "','dd-MM-yyyy') and trunc(regdt) <= TO_DATE('" + txtTo.Text + "','dd-MM-yyyy')"; if (txtsearchpara.Text != "" && txtsearchpara.Text != null) { Query += " and cmpno = '" + txtsearchpara.Text + "'"; } Query += " and lcocode='" + Session["username"].ToString() + "' order by INSDATE"; DataTable tblComplaintDetails = new DataTable(); Cls_Helper obj = new Cls_Helper(); tblComplaintDetails = obj.GetDataTable(Query); if (tblComplaintDetails.Rows.Count > 0) { GridCompList.DataSource = tblComplaintDetails; GridCompList.DataBind(); ViewState["Tbldetails"] = tblComplaintDetails; } else { lblmsg.Text = "No records found."; // MessageAlert("No records found.", "");//,"../Reports/RptCompDetails.aspx" } }
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 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 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 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); } }
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); } }
public DataTable GetTransations(string datestring, string whereClauseStr, string username, string dateStringparty) { try { Cls_Helper ObjHelper = new Cls_Helper(); string StrQry; //StrQry = "select x.lconame,x.lcoid,x.lcocode,(select openinbal from view_LCO_partyledgr_summ where dt=x.min_dt and lcocode=x.lcocode )openinbal," + // " x.drlimit,x.crlimit, x.companyname, x.distributor,x.subdistributor, x.cityname, x.statename, " + // " (select closingbal from view_LCO_partyledgr_summ where dt=x.max_dt and lcocode=x.lcocode )closingbal" + // " FRom (SELECT distid,PARENTID,hoid, dt,operid, lconame, lcoid, lcocode, sum(drlimit) drlimit , sum(crlimit) crlimit, min(dt) min_dt,max(dt)max_dt, companyname, distributor,subdistributor, cityname, statename" + // " FROM view_LCO_partyledgr_summ a " + // " group by lconame, lcoid, lcocode, companyname, distributor,subdistributor, cityname, statename, dt,operid,distid,PARENTID,hoid)x " + //" where " + whereClauseStr; StrQry = " select x.lconame,x.lcoid,x.lcocode,(select num_partled_openinbal from aoup_lcopre_lcoin_partyled_mst where trunc(dat_partled_date)=x.min_dt and var_partled_lcocode=x.lcocode )openinbal, "; StrQry += " x.drlimit,x.crlimit, x.companyname, x.distributor,x.subdistributor, x.cityname, x.statename, "; StrQry += " (select num_partled_closingbal from aoup_lcopre_lcoin_partyled_mst where trunc(dat_partled_date)=x.min_dt and var_partled_lcocode=x.lcocode )closingbal "; StrQry += " FRom "; StrQry += " (SELECT distid,PARENTID,hoid, operid, lconame, lcoid, lcocode, sum(drlimit) drlimit , sum(crlimit) crlimit, min(dt) min_dt,max(dt)max_dt, companyname, "; StrQry += " distributor,subdistributor, cityname, statename "; StrQry += " FROM view_lco_invpartyledgr_summ a "; StrQry += " where " + datestring + ""; StrQry += " group by lconame, lcoid, lcocode, companyname, distributor,subdistributor, cityname, statename, operid,distid,PARENTID,hoid)x "; if (whereClauseStr != "") { StrQry += " where " + whereClauseStr; } /* StrQry = " select lconame, lcoid, lcocode, (select NVL(a.num_partled_openinbal,0) from aoup_lcopre_lco_partyled_mst a "; * StrQry += " where dat_partled_date= "; * StrQry += " (select min(dat_partled_date) from aoup_lcopre_lco_partyled_mst b where x.lcoid =b.var_partled_lcoid and " + dateStringparty + ") "; * StrQry += " and x.lcoid=a.var_partled_lcoid)openinbal, "; * StrQry += " (select NVL(a.num_partled_closingbal,0) from aoup_lcopre_lco_partyled_mst a "; * StrQry += " where dat_partled_date= "; * StrQry += " (select max(dat_partled_date) from aoup_lcopre_lco_partyled_mst b where x.lcoid =b.var_partled_lcoid and " + dateStringparty + " ) "; * StrQry += " and x.lcoid=a.var_partled_lcoid) closingbal "; * StrQry += " ,sum(drlimit) drlimit , sum(crlimit) crlimit, "; * StrQry += " companyname,distributor,subdistributor, cityname, statename "; * StrQry += " from view_LCO_partyledgr_summ x "; * StrQry += " where " + datestring + " "; * StrQry += " and " + whereClauseStr + " "; * StrQry += " group by lconame, lcoid, lcocode, companyname, distributor,subdistributor, cityname, statename ";*/ return(ObjHelper.GetDataTable(StrQry)); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_RptINVLedger.cs-GetTransations"); return(null); } }
public void ProceedCancel() { string CustId = ""; string VcId = ""; string PlanName = ""; DataTable dt = new DataTable(); dt.Columns.Add("CustId"); dt.Columns.Add("VcId"); dt.Columns.Add("PlanName"); foreach (GridViewRow row in grdLcoCustEcsDetails.Rows) { if (row.RowType == DataControlRowType.DataRow) { CheckBox chkRow = (row.Cells[0].FindControl("chkautorenew") as CheckBox); if (chkRow.Checked) { CustId = row.Cells[1].Text; VcId = row.Cells[2].Text; PlanName = row.Cells[7].Text; dt.Rows.Add(CustId, VcId, PlanName); } } } /* string str = ""; * for (int i = 0; i < dt.Rows.Count; i++) * { * str += dt.Rows[i][0].ToString() + "$"+ dt.Rows[i][1].ToString() + "$" + dt.Rows[i][2].ToString() + "$"; * } * string strvalue = str.TrimEnd('$'); * * Hashtable ht = new Hashtable(); * ht.Add("username", username); * ht.Add("strvalue", strvalue); * * Cls_BLL_AutoRenewCancel obj = new Cls_BLL_AutoRenewCancel(); * string msg = obj.AutoRenewCancel(username, ht);*/ int cnt = 0; for (int i = 0; i < dt.Rows.Count; i++) { string str = " update aoup_lcopre_ecs_det set var_ecs_isactive ='C' "; str += " where var_ecs_vcid ='" + dt.Rows[i][1].ToString() + "'"; str += " and var_ecs_planname ='" + dt.Rows[i][2].ToString() + "'"; str += " and var_ecs_isactive ='" + "Y" + "'"; cnt++; Cls_Helper obj = new Cls_Helper(); int returnval = obj.insertQry(str); } BindData(); }
public DataTable GetReceiptData(string username, string receiptno) { try { Cls_Helper ObjHelper = new Cls_Helper(); string StrQry; if (receiptno.ToUpper().Contains("SPSN")) { StrQry = " select '' stbnopp, num_pistrans_id transid,var_pisnewstb_lcocode code,var_lcomst_name name,b.num_pisnewstb_rate STBRate,b.num_pisnewstb_discount STBDiscount, b.num_pisnewstb_net STBNet,b.num_pisnewstb_lcorate LCORate,b.num_pisnewstb_lcodiscount LCODiscount,b.num_pisnewstb_lconet LCONet,b.num_pisnewstb_netamount TotalNet,var_pistrans_paymode paymode, "; StrQry += " var_pistrans_cashier cashier,var_pisnewstb_stbcount stbcount,var_pisnewstb_stbpendingcount pendingcount,var_scheme_name scheme,var_pisnewstb_boxtype boxtype,var_pisnewstb_type type,ct.var_city_name City,sd.var_state_name State,VAR_SKYWORTH SKYWORTH,num_pisnewstb_faultycount Faulty,num_pisnewstb_foreclousrecount Foreclosre from aoup_lcopre_pis_trans_det a "; StrQry += " left outer join aoup_lcopre_pis_spnewstb_mst b on a.num_pistrans_transid=b.num_pistrans_transid "; StrQry += " left outer join aoup_lcopre_Scheme_master c on c.num_scheme_id=b.num_pisnewstb_scheme_id "; StrQry += " left outer join aoup_lcopre_lco_det l on l.var_lcomst_code=b.var_pisnewstb_lcocode "; StrQry += " inner join Aoup_lcopre_city_def ct on ct.num_city_id=a.num_pistrans_cityid inner join aoup_lcopre_state_def sd on sd.num_state_id=a.num_pistrans_stateid"; StrQry += " where var_pistrans_transtype='SP' and var_pistrans_receiptno='" + receiptno + "'"; // StrQry += " and var_pisnewstb_stbpendingcount<>0 "; } else if (receiptno.ToUpper().Contains("SPSR")) { StrQry = " select '' stbnopp, num_pisstbrpr_id transid,var_pisstbrpt_lco_code code,var_lcomst_name name,b.num_pisstbrpr_rate STBRate,num_pisstbrpr_discount STBDiscount,b.num_pisstbrpr_net STBNet,b.num_pisstbrpr_lcorate LCORate,b.num_pisstbrpr_lcodiscount LCODiscount,b.num_pisstbrpr_lconet LCONet,b.num_pisstbrpr_netamount TotalNet,var_pistrans_paymode paymode, var_pistrans_cashier cashier,var_pisstbrpr_stbcount stbcount,var_pisstbrpr_stbpendingcount pendingcount ,var_scheme_name scheme,var_pisnewstb_boxtype boxtype "; StrQry += " ,num_scheme_value schemevalue,var_pisnewstb_type type,ct.var_city_name City,sd.var_state_name State,VAR_SKYWORTH SKYWORTH,'0' Fualty,'0' Foreclosre from aoup_lcopre_pis_trans_det a "; StrQry += " left outer join aoup_lcopre_pis_spstbrepir_mst b on a.num_pistrans_transid=b.num_pisstbrpr_transid "; StrQry += " left outer join aoup_lcopre_scheme_master c on c.num_scheme_id=b.num_pisstbrpr_scheme_id "; StrQry += " left outer join aoup_lcopre_lco_det l on l.var_lcomst_code=b.var_pisstbrpt_lco_code "; StrQry += " inner join Aoup_lcopre_city_def ct on ct.num_city_id=a.num_pistrans_cityid inner join aoup_lcopre_state_def sd on sd.num_state_id=a.num_pistrans_stateid"; StrQry += " where var_pistrans_transtype='SP' and var_pistrans_receiptno='" + receiptno + "'"; StrQry += " and var_pisstbrpr_stbpendingcount<>0 "; } else if (receiptno.ToUpper().Contains("PPSN")) { StrQry = " SELECT var_pisnewstb_stbno stbnopp,num_pisnewstb_transid transid, var_pisnewstb_accno code, var_pisnewstb_vcno name,b.num_pisnewstb_rate STBRate,b.num_pisnewstb_discount STBDiscount,b.num_pisnewstb_net STBNet,b.num_pisnewstb_lcorate LCORate,b.num_pisnewstb_lcodiscount LCODiscount,b.num_pisnewstb_lconet LCONet,num_pisnewstb_amount TotalNet, var_pisnewstb_paymode paymode, var_pisnewstb_insby cashier, "; StrQry += " '1' stbcount,'1' pendingcount, var_scheme_name scheme,var_pisnewstb_boxtype boxtype ,num_scheme_value schemevalue ,'STB' type,ct.var_city_name City,sd.var_state_name State,VAR_SKYWORTH SKYWORTH,'0' Fualty,'0' Foreclosre FROM aoup_lcopre_pis_trans_det a LEFT OUTER JOIN "; StrQry += " aoup_lcopre_pis_ppnewstb b ON a.num_pistrans_transid = b.num_pisnewstb_transid LEFT OUTER JOIN aoup_lcopre_scheme_master c "; StrQry += " ON c.num_scheme_id = b.num_pisnewstb_schemeid inner join Aoup_lcopre_city_def ct on ct.num_city_id=a.num_pistrans_cityid inner join aoup_lcopre_state_def sd on sd.num_state_id=a.num_pistrans_stateid WHERE var_pistrans_transtype = 'PP' and var_pistrans_receiptno='"+ receiptno + "'"; StrQry += " and var_pisnewstb_warehouseuser is null "; } else { StrQry = " SELECT var_pisstbrpr_stbno stbnopp, num_pisstbrpr_transid transid, var_pisstbrpr_accno code,var_pisstbrpr_vcno name, num_pisstbrpr_rate STBRate,num_pisstbrpr_discount STBDiscount,num_pisstbrpr_net STBNet,num_pisstbrpr_lcorate LCORate,num_pisstbrpr_lcodiscount LCODiscount,num_pisstbrpr_lconet LCONet,num_pisstbrpr_amount TotalNet, var_pisstbrpr_paymode paymode, var_pisstrpr_insby cashier, "; StrQry += " '1' stbcount,'1' pendingcount, var_scheme_name scheme,var_pisstbrpr_boxtype boxtype,num_scheme_value schemevalue,'STB' type,ct.var_city_name City,sd.var_state_name State,VAR_SKYWORTH SKYWORTH,'0' Fualty,'0' Foreclosre FROM aoup_lcopre_pis_trans_det a LEFT OUTER JOIN "; StrQry += " aoup_lcopre_pis_ppstbrepair b ON a.num_pistrans_transid = b.num_pisstbrpr_transid LEFT OUTER JOIN aoup_lcopre_scheme_master c "; StrQry += " ON c.num_scheme_id = b.num_pisstbrpr_schemeid inner join Aoup_lcopre_city_def ct on ct.num_city_id=a.num_pistrans_cityid inner join aoup_lcopre_state_def sd on sd.num_state_id=a.num_pistrans_stateid WHERE var_pistrans_transtype = 'PP' and var_pistrans_receiptno='" + receiptno + "' "; StrQry += " and var_pistrpr_warehouseuser is null "; } return(ObjHelper.GetDataTable(StrQry)); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_Warehouse.cs"); return(null); } }
protected void loadLCOState() { DataSet dsStates = Cls_Helper.Comboupdate("AOUP_LCOPRE_STATE_DEF WHERE VAR_PLAN_COMPANYCODE='HWP' and num_state_id='" + ViewState["state"].ToString() + "' ORDER BY VAR_STATE_NAME", "NUM_STATE_ID", "VAR_STATE_NAME"); ddlState.DataSource = dsStates; ddlState.DataTextField = "VAR_STATE_NAME"; ddlState.DataValueField = "NUM_STATE_ID"; ddlState.DataBind(); dsStates.Dispose(); }
protected void loadState() { DataSet dsStates = Cls_Helper.Comboupdate("AOUP_LCOPRE_STATE_DEF", "NUM_STATE_ID", "VAR_STATE_NAME"); ddlState.DataSource = dsStates; ddlState.DataTextField = "VAR_STATE_NAME"; ddlState.DataValueField = "NUM_STATE_ID"; ddlState.DataBind(); ddlState.Items.Insert(0, new ListItem("-- Select State --", "")); }
public DataTable fillsentMsgs(string username, string from, string subject) { string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim(); OracleConnection conObj = new OracleConnection(ConStr); try { string _getIndata = "select a.num_messenger_id mid , a.num_msgto_subconvstnid msubid, a.var_messenger_subject msub, UTL_RAW.CAST_TO_VARCHAR2(a.var_messenger_message) mmsg, " + " a.var_messenger_from mfrom, " + " Case when var_messenger_toall='CN' then 'All Country LCO' " + " when var_messenger_toall='ST' then 'All '|| var_state_name ||'State LCO' " + " when var_messenger_toall='CT' then 'All '|| var_city_name ||'City LCO' else a.var_messenger_to end mto , " + " a.var_messenger_file mfile, a.dat_messenger_date mdate, a.var_messenger_insby mins, " + " a.dat_messenger_insdate minsdate, a.var_messenger_updby mupby, a.var_messenger_upddate mudate, a.var_messenger_type mtype, " + //UTL_RAW.CAST_TO_VARCHAR2(var_messenger_readby) readby, " var_messenger_toall msgtoall,var_messenger_toid msgtoallid,var_messenger_sentdelete sentdelete " + " from Aoup_Lcopre_Messenger a " + " left join aoup_lcopre_state_def b on a.var_messenger_toid=b.num_state_id " + " left join aoup_lcopre_city_def c on a.var_messenger_toid=c.num_city_id " + " where a.var_messenger_from ='" + username + "' and nvl(var_messenger_sentdelete,',') not like ('%" + username + ",%') "; if (from != "" && subject != "") { _getIndata += " and upper(a.var_messenger_to) like '%" + from.ToUpper() + "%' and upper(a.var_messenger_to) like '%" + from.ToUpper() + "%'"; } else if (from != "") { _getIndata += " and upper(a.var_messenger_to) like '%" + from.ToUpper() + "%'"; } else if (subject != "") { _getIndata += " and upper(a.var_messenger_subject) like '%" + subject.ToUpper() + "%'"; } else { _getIndata += " "; } _getIndata += " order by a.dat_messenger_date desc "; Cls_Helper ob = new Cls_Helper(); return(ob.GetDataTable(_getIndata)); } catch (Exception ex) { Cls_Security objSecurity = new Cls_Security(); objSecurity.InsertIntoDb(username, ex.Message.ToString(), "cls_data_messenger.cs-fillsentMsgs"); return(null); } finally { conObj.Close(); conObj.Dispose(); } }