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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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);
}
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 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 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 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 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 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);
}
}
public DataTable GetServiceData(string username, string catid, string operator_id)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
string whereString = "";
string from = DateTime.Today.ToString("dd-MMM-yyyy");
string to = DateTime.Today.ToString("dd-MMM-yyyy");
if (from != null && from != "")
{
whereString += " and trandt >= '" + from + "' ";
}
if (to != null && to != "")
{
whereString += " and trandt <= '" + to + "' ";
}
if (catid == "3")
{
whereString += " and lcode = '" + username + "' ";
}
//if (catid == "2")
//{
// whereString += " and MSOID= '" + operator_id + "' ";
//}
//else if (catid == "5")
//{
// whereString += " and DISTID= '" + operator_id + "' ";
//}
//else if (catid == "3")
//{
// whereString += " and LCOID= '" + operator_id + "' ";
//}
//else if (catid == "10")
//{
// whereString += " and hoid = '" + operator_id + "' ";
//}
StrQry = "SELECT a.msoid, a.msoname, a.distid, a.distname, a.lcoid, a.lconame, a.stbno, a.custno, a.addr, a.accpoid, a.svcpoid, a.vcid," +
" a.status, a.transby, a.transdt, a.orderid, a.reasonname" +
" FROM view_lcopre_service_actdact a " +
" where 1=1 " +
" " + whereString;
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_RptLCOAll-GetServiceData");
return(null);
}
}
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();
}
}
public DataTable GetLcopaymentDetails(string username, string prefixText, string category, string operid, string type, string BankName)
{
string ConStr = ConfigurationSettings.AppSettings["ConString"].ToString().Trim();
OracleConnection conObj = new OracleConnection(ConStr);
DataTable dt = new DataTable();
Cls_Helper ObjHelper = new Cls_Helper();
try
{
string str = "";
str = " SELECT a.lcocode, a.lconame, a.username, a.address, a.mobno, a.email, a.amt,a.name, a.paymode, a.bnknm, a.branch, a.receiptno, a.bank, a.chqddno, a.paydt, a.remrk,";
str += " a.chequedt, a.operid, a.opercategory, a.parentid, a.distid, a.hoid,a.isactive, a.company";
str += " FROM view_lcopre_payment_reprint a";
if (type == "0")
{
str += " where a.receiptno='" + prefixText + "'";
}
else
{
str += " where a.lcocode='" + prefixText + "'";
str += " and a.isactive='Y'";
}
if (category == "2")
{
str += " and a.parentid='" + operid.ToString() + "' ";
str += " and a.username ='******'";
}
else if (category == "5")
{
str += " and a.distid='" + operid.ToString() + "' ";
}
else if (category == "10")
{
str += " and a.hoid='" + operid.ToString() + "' ";
}
str += " and rownum <= 5";
dt = ObjHelper.GetDataTable(str);
return(dt);
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_TransHwayLcoReprintReceipt-GetLcopaymentDetails");
return(dt);
}
finally
{
conObj.Close();
conObj.Dispose();
}
}
public DataTable GetNewActivation(Hashtable htAddPlanParams, string username)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
string whereString = "";
string from = htAddPlanParams["from"].ToString();
string to = htAddPlanParams["to"].ToString();
string State = htAddPlanParams["State"].ToString();
string City = htAddPlanParams["City"].ToString();
string JV = htAddPlanParams["JV"].ToString();
string LCOCode = htAddPlanParams["LCOCode"].ToString();
if (from != null && from != "")
{
whereString += " and dt >= '" + from + "' ";
}
if (to != null && to != "")
{
whereString += " and dt <= '" + to + "' ";
}
if (State != "All")
{
whereString += " and c.var_state_name= '" + State + "' ";
}
if (City != "All" && City != "0")
{
whereString += " and a.City= '" + City + "' ";
}
if (JV != "0")
{
whereString += " and b.var_lcomst_company= '" + JV + "' ";
}
if (LCOCode != "" && LCOCode != null)
{
whereString += " and LCOCode = '" + LCOCode + "' ";
}
StrQry = " select a.cafno,a.owner,a.accno,a.name,a.mobile,a.landline,a.zip,a.vcid,a.stb,dt,b.var_lcomst_company JVNAME,a.city,c.var_state_name state,b.var_lcomst_dasarea Das from ";
StrQry += " view_rptcrf_details a,aoup_lcopre_lco_det b,view_lcopre_pan_def c where a.owner=b.var_lcomst_code";
StrQry += " and a.city=c.var_city_name";
StrQry += " and custflag='WEB'";
StrQry += " " + whereString;
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "Cls_Data_rptNewActivation-GetNewActivation");
return(null);
}
}
public DataTable lcoprecolldetails(Hashtable htAddPlanParams, string username, string operid, string catid)
{
try
{
string from = htAddPlanParams["from"].ToString();
string to = htAddPlanParams["to"].ToString();
string search_type = htAddPlanParams["search"].ToString().Trim();
string txtsearch = htAddPlanParams["txtsearch"].ToString().Trim();
Cls_Helper ObjHelper = new Cls_Helper();
string StrQry;
StrQry = "SELECT a.account_no, a.customer_name, a.entity_code, a.lco_name, a.city, " +
" a.state, a.area, a.receipt_no, a.amount, a.receipt_date, " +
" a.reversal_date, a.created_by_username, a.description, " +
" a.customer_type, a.payment_mode, a.cheque_no, a.cheque_date," +
" a.bank_name, a.branch_name, a.bank_code, a.payment_channel, " +
" a.upass_reciept_no, a.reversal_status, a.jv, a.distributer, " +
" a.SUB_DISTRIBUTER, a.company, a.report_date, a.from_date, a.to_date,a.vc" +
" FROM view_splcoprecolldetails a " +
" where Trunc(a.date1) >='" + from + "'" +
" and Trunc(a.date1) <='" + to + "'";
if (catid == "3")
{
StrQry += " and a.entity_code = '" + username + "'";
}
if (txtsearch.Length > 0)
{
if (search_type == "0")
{
StrQry += @" and upper(""Account No"")= upper('" + txtsearch + "')";
}
else if (search_type == "1")
{
StrQry += @" and upper(a.a.vc)= upper('" + txtsearch + "')";
}
else if (search_type == "2")
{
StrQry += @" and upper(""Lco Code"")= upper('" + txtsearch + "')";
}
}
return(ObjHelper.GetDataTable(StrQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb(username, ex.Message.ToString(), "cls_Data_lcoprecolldetails.cs-lcoprecolldetails");
return(null);
}
}
public DataTable GetSelfCareDetails(string strQry)
{
try
{
Cls_Helper ObjHelper = new Cls_Helper();
return(ObjHelper.GetDataTable(strQry));
}
catch (Exception ex)
{
Cls_Security objSecurity = new Cls_Security();
objSecurity.InsertIntoDb("admin_ho", ex.Message.ToString(), "cls_data_RptSelfCareDetails.cs");
return(null);
}
}