public string GetSearchResult(string user_id, string fname, string lname, string bug_id)
{
RadDBImpl _dbImpl = new RadDBImpl();
DataTable dt = new DataTable();
string sql = "select LC.NAME,C.CLIENT_NAME, P.BUG_ID,P.PROJECT_TYPE_ID,P.BUG_DESC,P.START_DT,P.END_DT,P.USER_ID,P.STATUS,P.ACTION_TAKEN,P.SESSION_ID,P.CONTACT_PERSON " +
" FROM PROJECT P,LKP_COMPANY LC,CLIENT C , LKP_USERS U " +
" WHERE P.USER_ID=U.USER_ID AND P.STATUS='Y' AND P.CLIENT_ID = C.CLIENT_ID AND P.COMPANY_ID = LC.COMPANY_ID";
if (string.IsNullOrEmpty(user_id) && string.IsNullOrEmpty(fname) &&
string.IsNullOrEmpty(lname) && string.IsNullOrEmpty(bug_id))
{
dt = _dbImpl.GetDataSet(sql).Tables[0];
}
else
{
if (!string.IsNullOrEmpty(user_id))
{
sql = sql + " AND P.USER_ID='" + user_id.ToUpper() + "'";
}
if (!string.IsNullOrEmpty(fname))
{
sql = sql + " AND U.FIRST_NAME='" + fname.ToUpper() + "'";
}
if (!string.IsNullOrEmpty(lname))
{
sql = sql + " AND U.LAST_NAME='" + lname.ToUpper() + "'";
}
if (!string.IsNullOrEmpty(bug_id))
{
sql = sql + " AND P.BUG_ID='" + bug_id.ToUpper() + "'";
}
sql = sql + " ORDER BY P.START_DT DESC";
dt = _dbImpl.GetDataSet(sql).Tables[0];
}
dt.Columns.Add("START", typeof(string));
dt.Columns.Add("END", typeof(string));
foreach (DataRow rw in dt.Rows)
{
string startdt = rw["START_DT"].ToString();
rw["START"] = startdt;
string enddt = rw["END_DT"].ToString();
rw["END"] = enddt;
}
return(GetJson(dt));
}
public string GetCompanyClients(string companyId)
{
string sql = string.Empty;
RadDBImpl _dbImpl = new RadDBImpl();
if (string.IsNullOrEmpty(companyId))
{
sql = "select A.CLIENT_ID,CLIENT_NAME from CLIENT A,CLIENT_COMPANY B WHERE A.CLIENT_ID = B.CLIENT_ID";
}
else
{
sql = "select A.CLIENT_ID,CLIENT_NAME from CLIENT A,CLIENT_COMPANY B WHERE A.CLIENT_ID = B.CLIENT_ID AND COMPANY_ID = '" + companyId.ToUpper() + "'";
}
DataTable dt = _dbImpl.GetDataSet(sql).Tables[0];
foreach (DataRow rw in dt.Rows)
{
string CLIENT_ID = rw["CLIENT_ID"].ToString();
rw["CLIENT_ID"] = CLIENT_ID;
string CLIENT_NAME = rw["CLIENT_NAME"].ToString();
rw["CLIENT_NAME"] = CLIENT_NAME;
}
return(GetJson(dt));
}
public string GetCompany(string companyId)
{
string sql = string.Empty;
RadDBImpl _dbImpl = new RadDBImpl();
if (string.IsNullOrEmpty(companyId))
{
sql = "select company_id , name,company_no from lkp_company";
}
else
{
sql = "select company_id,name ,company_no from lkp_company where company_id = '" + companyId.ToUpper() + "'";
}
DataTable dt = _dbImpl.GetDataSet(sql).Tables[0];
foreach (DataRow rw in dt.Rows)
{
string company_id = rw["company_id"].ToString();
rw["company_id"] = company_id;
string name = rw["name"].ToString();
rw["name"] = name;
string company_no = rw["company_no"].ToString();
rw["company_no"] = company_no;
}
return(GetJson(dt));
}
public string GetProject_Type_Id()
{
RadDBImpl _dbImpl = new RadDBImpl();
string sql = "select * from LKP_PROJECT_TYPE";
DataTable dt = _dbImpl.GetDataSet(sql).Tables[0];
return(GetJson(dt));
}
public DataTable getUserInformation(string deviceId)
{
DataTable dt = new DataTable();
RadDBImpl _dbImpl = new RadDBImpl();
try
{
string sql = "select device_name,phone_no,service_provider, userid,regkey from device a,device_users b where a.device_id = '" + deviceId + "' and a.device_id = b.device_id and active ='Y'";
dt = _dbImpl.GetDataSet(sql).Tables[0];
}
catch {}
return(dt);
}
public DataTable getUserLoginInfo(string userId)
{
DataTable dt = new DataTable();
RadDBImpl _dbImpl = new RadDBImpl();
try
{
string sql = "select question,answer,password, userid from users where userid = '" + userId.ToUpper() + "'";
dt = _dbImpl.GetDataSet(sql).Tables[0];
}
catch (Exception e)
{
}
return(dt);
}
public string GetClient(string clientId)
{
string sql = string.Empty;
RadDBImpl _dbImpl = new RadDBImpl();
if (string.IsNullOrEmpty(clientId))
{
sql = "select a.client_id,client_name,amt_charge,a.billing_Id,name ,b.company_id, bill_to from client a, CLIENT_COMPANY b ,lkp_company c , lkp_billing d where a.client_id = b.client_id and b.company_id = c.company_id and a.billing_id = d.billing_id ";
}
else
{
sql = "select a.client_id,client_name,amt_charge,a.billing_Id ,name,b.company_id, bill_to from client a, CLIENT_COMPANY b ,lkp_company c , lkp_billing d where a.client_id = b.client_id and b.company_id = c.company_id and a.billing_id = d.billing_id and client_id = '" + clientId.ToUpper() + "'";
}
DataTable dt = _dbImpl.GetDataSet(sql).Tables[0];
//dt.Columns.Add("client_id", typeof(string));
//dt.Columns.Add("client_name", typeof(string));
//dt.Columns.Add("amt_charge", typeof(Double));
//dt.Columns.Add("billing_id", typeof(string));
foreach (DataRow rw in dt.Rows)
{
string client_id = rw["client_id"].ToString();
rw["client_id"] = client_id;
string client_name = rw["client_name"].ToString();
rw["client_name"] = client_name;
double amt_charge = Convert.ToDouble(rw["amt_charge"]);
rw["amt_charge"] = amt_charge;
string billing_Id = rw["billing_Id"].ToString();
rw["billing_Id"] = billing_Id;
string bill_to = rw["bill_to"].ToString();
rw["bill_to"] = bill_to;
string name = rw["name"].ToString();
rw["name"] = name;
string company_id = rw["company_id"].ToString();
rw["company_id"] = company_id;
}
return(GetJson(dt));
}
public DataTable GetReportDS()
{
string sql = string.Empty;
RadDBImpl _dbImpl = new RadDBImpl();
sql = "SELECT BILL.BILLING_ID, BILL.BILL_TO, BILL.CONTACT_PERSON, BILL.ADDRESS, "
+ " BILL.CITY, BILL.STATE, BILL.COUNTRY, BILL.ZIP,CL.CLIENT_NAME, "
+ " CL.AMT_CHARGE,CL.CLIENT_ID,P.BUG_ID, P.BUG_DESC, P.START_DT, P.END_DT "
+ " FROM LKP_BILLING BILL, CLIENT CL, PROJECT P "
+ " WHERE BILL.BILLING_ID = CL.BILLING_ID AND CL.CLIENT_ID = P.CLIENT_ID";
DataTable dt = _dbImpl.GetDataSet(sql).Tables[0];
return(dt);
}
public string GetBillingDetails(string billingId)
{
string sql = string.Empty;
RadDBImpl _dbImpl = new RadDBImpl();
if (string.IsNullOrEmpty(billingId))
{
sql = "select billing_id,bill_to,contact_person,address,city,state,country,zip from lkp_billing";
}
else
{
sql = "select billing_id,bill_to,contact_person,address,city,state,country,zip from lkp_billing where billing_id = '" + billingId.ToUpper() + "'";
}
DataTable dt = _dbImpl.GetDataSet(sql).Tables[0];
foreach (DataRow rw in dt.Rows)
{
string billing_Id = rw["billing_id"].ToString();
rw["billing_id"] = billing_Id;
string bill_to = rw["bill_to"].ToString();
rw["bill_to"] = bill_to;
string contact_person = rw["contact_person"].ToString();
rw["contact_person"] = contact_person;
string BillAddress = rw["address"].ToString();
rw["address"] = BillAddress;
string Bill_city = rw["city"].ToString();
rw["city"] = Bill_city;
string Bill_state = rw["state"].ToString();
rw["state"] = Bill_state;
string Bill_country = rw["country"].ToString();
rw["country"] = Bill_country;
string bill_zip = rw["zip"].ToString();
rw["zip"] = bill_zip;
}
return(GetJson(dt));
}
public string GetProjectDetail(string bug_id)
{
RadDBImpl _dbImpl = new RadDBImpl();
string sql = "select A.* , C.NAME, D.CLIENT_NAME from project A , CLIENT_COMPANY B , LKP_COMPANY C , CLIENT D where A.CLIENT_ID = B.CLIENT_ID AND A.COMPANY_ID = B.COMPANY_ID AND B.COMPANY_ID= C.COMPANY_ID AND B.CLIENT_ID = D.CLIENT_ID AND A.CLIENT_ID= D.CLIENT_ID AND bug_id='" + bug_id.ToUpper() + "'";
DataTable dt = _dbImpl.GetDataSet(sql).Tables[0];
dt.Columns.Add("START", typeof(string));
dt.Columns.Add("END", typeof(string));
foreach (DataRow rw in dt.Rows)
{
string startdt = rw["START_DT"].ToString();
rw["START"] = startdt;
string enddt = rw["END_DT"].ToString();
rw["END"] = enddt;
}
return(GetJson(dt));
}
public DataTable GetLocation(string deviceId)
{
DataTable dt = new DataTable();
RadDBImpl _dbImpl = new RadDBImpl();
try
{
if (string.IsNullOrEmpty(deviceId))
{
return(null);
}
else
{
// string sql = "select DT,LATITUDE,LONGITUDE from GeoLocation where REGKEY = '" + regkey + "' and rownum <= 5 order by dt desc";
string sql = "select DT,LATITUDE,LONGITUDE from GeoLocation where device_id = '" + deviceId + "' order by dt desc";
dt = _dbImpl.GetDataSet(sql).Tables[0];
}
}
catch {}
return(dt);
}
public bool updateUserDevice(string deviceId, string regKey, string UserId, string oldUserId)
{
RadDBImpl _dbImpl = new RadDBImpl();
if (string.IsNullOrEmpty(regKey) || string.IsNullOrEmpty(UserId) || string.IsNullOrEmpty(deviceId)
)
{
return(false);
}
else
{
string password = string.Empty;
string ques = string.Empty;
string ans = string.Empty;
string active = string.Empty;
IRadDbTransaction objTransaction = new RadDbTransaction();
bool isActive = isUserActive(oldUserId);
string updSql = "update users set active = 'N' where userId = '" + oldUserId + "'";
string updateSql = "update device_users set active = 'N' where device_id ='" + deviceId + "' and userId = '" + oldUserId + "'";
bool isUserExists = checkUserExists(UserId);
if (isUserExists)
{
string sql = "update users set active = 'Y' where userId = '" + UserId + "'";
string sql1 = "update device_users set active = 'Y' where device_id ='" + deviceId + "' and userId = '" + UserId + "'";
objTransaction.AddSql(sql);
objTransaction.AddSql(sql1);
}
else
{
string sql = "select password,question,answer,active from users where userId = '" + oldUserId.ToUpper() + "'";
DataTable dt = _dbImpl.GetDataSet(sql).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
password = dt.Rows[0]["password"].ToString();
ques = dt.Rows[0]["question"].ToString();
ans = dt.Rows[0]["answer"].ToString();
active = dt.Rows[0]["active"].ToString();
}
if (!isUserExists)
{
string updateDevice = "update device set REGKEY = '" + regKey + "' where device_id = '" + deviceId + "'";
string insertUser = "******" +
" VALUES ('" + UserId + "', '" + password + "','" + ques + "','" + ans + "' ,'Y', sysdate )";
string insertDeviceUser = "******" +
" VALUES ('" + deviceId + "', '" + UserId + "','Y', sysdate )";
objTransaction.AddSql(updateDevice);
objTransaction.AddSql(insertUser);
objTransaction.AddSql(insertDeviceUser);
}
}
objTransaction.AddSql(updSql);
objTransaction.AddSql(updateSql);
try
{
objTransaction.ExecuteTransaction();
Logging.WriteMessage("Update User succeedd");
return(true);
}
catch {
Logging.WriteError("Update User Failed");
return(false);
}
}
}