//Get customer list
public DataTable getCusomerlist()
{
DataTable custList = new DataTable();
try
{
FileLogger.AppendLog("Access", "RawDataBusinessLogic=>getCusomerlist", "Start getting Customer list");
/* string query = @"select distinct MasterCustomerID,dbstring,Specility as 'Domain' from [License Server].[dbo].[licensemaster]
* where user_type='Super Admin' and isNull(MasterCustomerID,'')<>'' and
* firstname not like '%qa%' and firstname not like '%test%' and firstname not like '%demo%' and
* lastname not like '%qa%' and lastname not like '%test%' and lastname not like '%demo%'"; */
/* string query = @"select distinct Top 100 MasterCustomerID,dbstring,Specility as 'Domain' from [License Server].[dbo].[licensemaster]
* where user_type='Super Admin' and Specility='Dental' and dbstring like '%Host=10.67.206.111;%' "; */
string query = @"select MasterCustomerID,dbstring,Specility as 'Domain' from [License Server].[dbo].[licensemaster]
where user_type='Super Admin' and Specility='Dental' and MasterCustomerID in ('d10648') ";
custList = DBmain.getDataFromSQLServer(query);
FileLogger.AppendLog("Access", "RawDataBusinessLogic=>getCusomerlist", "Finish getting Customer list");
}
catch (Exception Ex)
{
FileLogger.AppendLog("Error", "getCusomerlist()", Ex.Message);
}
return(custList);
}
//Get query list
public DataTable getQuerylist()
{
DataTable custList = new DataTable();
try
{
FileLogger.AppendLog("Access", "RawDataBusinessLogic=>getQuerylist", "Start getting Query list");
string query = @"select FieldName,FieldQuery from [SCD].[DBO].[AllCustomerRawDataQuery];";
custList = DBmain.getDataFromSQLServer(query);
FileLogger.AppendLog("Access", "RawDataBusinessLogic=>getQuerylist", "Finish getting Query list");
}
catch (Exception Ex)
{
FileLogger.AppendLog("Error", "getQuerylist()", Ex.Message);
}
return(custList);
}
public static DataTable getRefreshDetailFromSCD(string MID, string CID)
{
// CustID in AllPracticename is PracticeID in UploadedSettings
DataTable RefData = new DataTable();
try
{
string query = string.Format(@"select US.SPUID,US.LastRefreshDate,case when (custmaster.isInactive=1) then 'InActive' else 'Active' end as 'SPUStatus' from
(
select techid as 'SPUID',
convert(varchar(11), isNull([WebSync], ''), 121) as 'LastRefreshDate',
convert(varchar(11), isNull([SynchronizationID], ''), 121) as 'MasterCustomerID',
convert(varchar(11), isNull([PracticeID], ''),121 )as 'PracticeID'
from
(select techid,[sub],[value] from[SCD].DBO.UploadedSettings where filename = 'SSCSettings.ini' and isNull(value, '') <> '') as result
Pivot
(
Max([value])
for sub in
(
[SynchronizationID],
[WebSync],
[PracticeID]
)
) as p
) as US
left join[SCD].DBO.custmaster on US.SPUID = custmaster.techid
where US.MasterCustomerID = '{0}' and US.PracticeID = '{1}'", MID, CID);
RefData = DBmain.getDataFromSQLServer(query);
}
catch (Exception Ex)
{
FileLogger.AppendLog(MID + "-Error", "getRefreshDetailFromSCD=>" + MID, Ex.Message);
}
return(RefData);
}
//Get data of single account
public static int getSingleAccountDetail(string MID, string D, string DBString, DataTable querydt)
{
try
{
FileLogger.AppendLog(MID + "-Access", "AllCustomerRowData=>>getSingleAccountDetail", "====Start getting data of " + MID + "====");
List <Detail> lstDetail = new List <Detail>();
bool ret = false;
try
{
//get All practicename data
string query = "select distinct PracticeID,CustID,Practicename,city,state,zip from allpracticenames where practiceid is not null";
DataTable allPracticeNameData = DBmain.getDataFromMySQL(DBString, query);
FileLogger.AppendLog(MID + "-Access", "AllCustomerRowData=>>getSingleAccountDetail", "====Start getting data of " + MID + "====");
if (allPracticeNameData != null && allPracticeNameData.Rows.Count > 0)
{
//Practicename & Custid loop
foreach (DataRow drAPN in allPracticeNameData.Rows)
{
//Empty list
lstDetail = new List <Detail>();
// lopping query 1 by 1
foreach (DataRow drqr in querydt.Rows)
{
//Replacing custid & practiceid in query
string query1 = Convert.ToString(drqr["FieldQuery"]).Trim().Replace("rawdatacustid", Convert.ToString(drAPN["CustID"]).Trim()).Replace("rawdatapracticeid", Convert.ToString("'" + drAPN["PracticeID"] + "'").Trim());
DataTable queryResult = DBmain.getDataFromMySQL(DBString, query1);
if (queryResult != null && queryResult.Rows.Count > 0)
{
//add query data into list
Detail detaiTemp = new Detail();
detaiTemp.MastercustomerID = MID;
detaiTemp.FieldName = Convert.ToString(drqr["FieldName"]).Trim();
detaiTemp.value = Convert.ToString(queryResult.Rows[0][0]).Trim();
lstDetail.Add(detaiTemp);
}
else
{
//ad if ther is no data for query
Detail detaiTemp = new Detail();
detaiTemp.MastercustomerID = MID;
detaiTemp.FieldName = Convert.ToString(drqr["FieldName"]).Trim();
detaiTemp.value = "No Data";
lstDetail.Add(detaiTemp);
}
}
//Practicename
Detail detailPracticename = new Detail();
detailPracticename.MastercustomerID = MID;
detailPracticename.FieldName = "Practicename";
detailPracticename.value = Convert.ToString(drAPN["Practicename"]).Trim();
lstDetail.Add(detailPracticename);
//city
Detail detailcity = new Detail();
detailcity.MastercustomerID = MID;
detailcity.FieldName = "City";
detailcity.value = Convert.ToString(drAPN["city"]).Trim();
lstDetail.Add(detailcity);
//state
Detail detailstate = new Detail();
detailstate.MastercustomerID = MID;
detailstate.FieldName = "State";
detailstate.value = Convert.ToString(drAPN["state"]).Trim();
lstDetail.Add(detailstate);
//zip
Detail detailzip = new Detail();
detailzip.MastercustomerID = MID;
detailzip.FieldName = "Zip";
detailzip.value = Convert.ToString(drAPN["zip"]).Trim();
lstDetail.Add(detailzip);
//Domain
Detail detailDomain = new Detail();
detailDomain.MastercustomerID = MID;
detailDomain.FieldName = "Domain";
detailDomain.value = D;
lstDetail.Add(detailDomain);
//Get last refresh detail & SPU status
DataTable refData = getRefreshDetailFromSCD(MID, drAPN["CustID"].ToString().Trim());
if (refData != null && refData.Rows.Count > 0)
{
//SPUID
Detail detailSPUID = new Detail();
detailSPUID.MastercustomerID = MID;
detailSPUID.FieldName = "SPUID";
detailSPUID.value = Convert.ToString(refData.Rows[0]["SPUID"]);
lstDetail.Add(detailSPUID);
//LastRefreshDate
Detail detailLastRefreshDate = new Detail();
detailLastRefreshDate.MastercustomerID = MID;
detailLastRefreshDate.FieldName = "LastRefreshDate";
detailLastRefreshDate.value = Convert.ToString(refData.Rows[0]["LastRefreshDate"]);
lstDetail.Add(detailLastRefreshDate);
//SPUStatus
Detail detailSPUStatus = new Detail();
detailSPUStatus.MastercustomerID = MID;
detailSPUStatus.FieldName = "SPUStatus";
detailSPUStatus.value = Convert.ToString(refData.Rows[0]["SPUStatus"]);
lstDetail.Add(detailSPUStatus);
}
else
{
//SPUID
Detail detailSPUID = new Detail();
detailSPUID.MastercustomerID = MID;
detailSPUID.FieldName = "SPUID";
detailSPUID.value = "Not Uploaded in SCD Log";
lstDetail.Add(detailSPUID);
//LastRefreshDate
Detail detailLastRefreshDate = new Detail();
detailLastRefreshDate.MastercustomerID = MID;
detailLastRefreshDate.FieldName = "LastRefreshDate";
detailLastRefreshDate.value = "Not Uploaded in SCD Log";
lstDetail.Add(detailLastRefreshDate);
//SPUStatus
Detail detailSPUStatus = new Detail();
detailSPUStatus.MastercustomerID = MID;
detailSPUStatus.FieldName = "SPUStatus";
detailSPUStatus.value = "Not Uploaded in SCD Log";
lstDetail.Add(detailSPUStatus);
}
//create list of query
List <string> queryList = new List <string>();
string PracticeID = Convert.ToString(drAPN["PracticeID"]).Trim();
int CustID = Convert.ToInt32(drAPN["CustID"].ToString().Trim());
//Add current raw data of current masterid into AllCustomerRawDataHistory table
queryList.Add("insert into [SCD].dbo.[AllCustomerRawDataHistory] select * from [SCD].[dbo].[AllCustomerRawData] where [AllCustomerRawData].mastercustomerid='" + MID + "' and [AllCustomerRawData].[PracticeID]='" + PracticeID + "' and [AllCustomerRawData].[CustID]=" + CustID);
//delete existing data current masterid from [AllCustomerRawData] to fill new data
queryList.Add("delete from [SCD].[dbo].[AllCustomerRawData] where [AllCustomerRawData].mastercustomerid='" + MID + "' and [AllCustomerRawData].[PracticeID]='" + PracticeID + "' and [AllCustomerRawData].[CustID]=" + CustID);
//fill new data one by on
foreach (Detail dt in lstDetail)
{
string tempQuery = "insert into [SCD].dbo.[AllCustomerRawData](MasterCustomerID,FieldName,[Value],[InnsertedTime],[PracticeID],[CustID]) values('" + dt.MastercustomerID + "','" + dt.FieldName + "','" + dt.value + "',GetDate(),'" + PracticeID + "'," + CustID + ")";
queryList.Add(tempQuery);
}
ret = DBmain.setSQLServerQueryListInDb(queryList, MID, PracticeID, CustID);
}
}
else
{
Detail detaiTemp = new Detail();
detaiTemp.MastercustomerID = MID;
detaiTemp.FieldName = "EmptyAllPracticeNames";
detaiTemp.value = "allpracticenames is empty";
lstDetail.Add(detaiTemp);
List <string> queryList = new List <string>();
//Add current raw data of current masterid into AllCustomerRawDataHistory table
queryList.Add("insert into [SCD].dbo.[AllCustomerRawDataHistory] select * from [SCD].[dbo].[AllCustomerRawData] where [AllCustomerRawData].mastercustomerid='" + MID + "'");
//delete existing data current masterid from [AllCustomerRawData] to fill new data
queryList.Add("delete from [SCD].[dbo].[AllCustomerRawData] where [AllCustomerRawData].mastercustomerid='" + MID + "'");
//insert data
string tempQuery = "insert into [SCD].dbo.[AllCustomerRawData](MasterCustomerID,FieldName,Value,InnsertedTime) values('" + detaiTemp.MastercustomerID + "','" + detaiTemp.FieldName + "','" + detaiTemp.value + "',GetDate())";
queryList.Add(tempQuery);
ret = DBmain.setSQLServerQueryListInDb(queryList, MID, "NoPracticeID", 0);
}
}
catch (Exception Ex)
{
FileLogger.AppendLog(MID + "-Error", "getSingleAccountDetail=>" + MID, Ex.Message);
}
if (ret)
{
FileLogger.AppendLog(MID + "-Access", "AllCustomerRowData=>>getSingleAccountDetail", " Successfully getting data of " + MID);
}
else
{
FileLogger.AppendLog(MID + "-Access", "AllCustomerRowData=>>getSingleAccountDetail", " Some error in getting data of " + MID);
}
FileLogger.AppendLog(MID + "-Access", "AllCustomerRowData=>>getSingleAccountDetail", "====Finish getting data of " + MID + "====");
// return ret;
}
catch (Exception Ex)
{
}
return(1);
}