public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
string sqlQuery = @"SELECT
opp.OpportunityId
,opp.Name
,opp.CustomerId
,opp.CustomerIdName
,opp.OwnerIdName
,sm.Value AS Status
,opp.new_relatedactivitystatusidName AS ActivityStatus
,opp.CreatedOn
,opp.ActualCloseDate
FROM
Opportunity AS opp (NOLOCK)
JOIN
StringMap AS sm (NOLOCK)
ON
sm.ObjectTypeCode=3
AND
sm.AttributeName='statuscode'
AND
sm.AttributeValue=opp.StatusCode
WHERE
opp.OwningBusinessUnit='4D2ABB3A-C2B1-E411-80C7-005056A60603'";
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
string sqlQuery = @"SELECT
app.ActivityId
,app.new_activitytopicidName AS ActivityTopic
,app.new_activitystatusName AS ActivityStatus
,app.new_activitystatusdetailName AS ActivityStatusDetail
,sm.Value AS PresentationType
,app.new_salesofficeidName AS SalesOffice
,app.OwnerIdName
,app.ScheduledStart
,app.ActualEnd
,ap.PartyIdName AS CustomerIdName
,smState.Value AS State
,ap.PartyId AS CustomerId
FROM
Appointment AS app (NOLOCK)
JOIN
ActivityParty AS ap (NOLOCK)
ON
ap.ActivityId=app.ActivityId
AND
ap.ParticipationTypeMask=5 --Required Attendies
LEFT JOIN
StringMap AS sm (NOLOCK)
ON
sm.ObjectTypeCode=4201
AND
sm.AttributeName='new_presentationtype'
AND
sm.AttributeValue=app.new_presentationtype
JOIN
StringMap AS smState (NOLOCK)
ON
smState.ObjectTypeCode=4201
AND
smState.AttributeName='statecode'
AND
smState.AttributeValue=app.StateCode
WHERE
app.OwningBusinessUnit='4D2ABB3A-C2B1-E411-80C7-005056A60603'";
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
string sqlQuery = @"SELECT
q.QuoteId
,q.Name
,CASE
WHEN q.new_paymentplan=0 THEN 'Kredili Ödeme Planı'
WHEN q.new_paymentplan=1 THEN 'Kredisiz Ödeme Planı'
END AS PaymentPlan
,q.TotalLineItemAmount AS ItemAmount
,Sm.Value SalesStatus
,q.new_contractprocessdate AS ContractProcessDate
,q.new_contractdate AS ContractDate
,q.CreatedOn
,q.new_persquaremeter AS PerSquareMeter
,q.new_taxamount AS KDVAmount
,q.new_taxofstampamount AS TaxOfStampAmount
,q.new_amountwithtax AS AmountWithKdv
,q.new_totalsalesamountbytax AS TotalSalesAmount
,q.new_prepaymentamount AS PrePaymentAmount
,smPrePayment.Value AS PrePaymentType
,q.new_financialaccountidName AS FinancialAccountCode
,q.new_contractnumber AS ContractNumber
,q.CustomerId
,q.CustomerIdName
,q.OwnerId
,q.OwnerIdName
,q.new_productid
,q.new_productidName
,CASE WHEN q.CustomerIdType = 1 THEN 'Account'
WHEN q.CustomerIdType = 2 THEN 'Contact'
END AS 'CustomerType'
FROM
Quote AS q (NOLOCK)
JOIN
Product AS p (NOLOCK)
ON
q.new_productid=p.ProductId
JOIN
new_project AS pro (NOLOCK)
ON
p.new_projectid=pro.new_projectId
JOIN
new_projectsalescollaborate AS pcol (NOLOCK)
ON
pro.new_projectId=pcol.new_projectid
JOIN
new_collaborateaccount AS col (NOLOCK)
ON
pcol.new_accountid=col.new_collaborateaccountId
LEFT JOIN
StringMap AS smPrePayment (NOLOCK)
ON
smPrePayment.ObjectTypeCode=1084
AND
smPrePayment.AttributeName='new_prepaymenttype'
AND
smPrePayment.AttributeValue=q.new_prepaymenttype
JOIN
StringMap AS sm (NOLOCK)
ON
sm.ObjectTypeCode=1084
AND
sm.AttributeName='statuscode'
AND
sm.AttributeValue=q.StatusCode
WHERE
pro.new_projectId!='CB9CC4EF-1117-E011-817F-00123F4DA0F7'
AND
col.new_collaborateaccountId='B3A17FFD-C5B1-E411-80C7-005056A60603'";
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
string sqlQuery = @"SELECT
p.new_webformId AS Id
,p.new_name As Name
,pro.new_projectId AS ProjectId
,pro.new_name AS ProjectIdName
,sm.Value AS Status,
new_contactid AS ContactId,
new_contactidname AS ContactIdName,
new_emailadress AS EmailAddress,
new_firstname AS FirstName,
new_lastname AS LastName,
new_message AS Message,
new_mobilephone AS MobilePhone,
new_channelofawarenessid AS ChannelOfAwarenessId,
new_channelofawarenessidname AS ChannelOfAwarenessIdName,
new_isemailadress AS IsEmailAddress,
new_istakeinfo AS IsTakeInfo
FROM
new_webform AS p (NOLOCK)
JOIN
new_project AS pro (NOLOCK)
ON
p.new_projectid=pro.new_projectId
JOIN
new_projectsalescollaborate AS pcol (NOLOCK)
ON
pro.new_projectId=pcol.new_projectid
JOIN
new_collaborateaccount AS col (NOLOCK)
ON
pcol.new_accountid=col.new_collaborateaccountId
JOIN
StringMap AS sm (NOLOCK)
ON
sm.ObjectTypeCode=10058
AND
sm.AttributeName='statuscode'
AND
sm.AttributeValue=p.StatusCode
WHERE
pro.new_projectId!='CB9CC4EF-1117-E011-817F-00123F4DA0F7'
AND
col.new_collaborateaccountId='B3A17FFD-C5B1-E411-80C7-005056A60603'";
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
public ICollaborateData GetCollaborationData(CollaborateDataType dataType)
{
if (!dictionary.ContainsKey(dataType))
{
throw new Exception(string.Format("A GetCollaborate method of type '{0}' cannot be found", dataType.ToString()));
}
return(dictionary[dataType]);
}
public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
#region | Oldiest Code |
// string sqlQuery = @"SELECT
// c.ContactId
// ,c.FirstName
// ,c.LastName
// ,c.new_tcidentitynumber AS IdentityNumber
// ,c.MobilePhone
// ,c.EMailAddress1 AS EmailAddress
// ,smGender.Value AS Gender
// ,smJob.Value AS JobTitle
// ,c.new_countryidName AS Country
// ,c.new_addresscityidName AS City
// ,c.new_addresstownidName AS Town
// ,c.new_addressdistrictidName AS District
// ,c.new_addressdetail AS AddressDetail
// ,smType.Value AS CustomerType
// FROM
// Contact AS c (NOLOCK)
// LEFT JOIN
// StringMap AS smGender (NOLOCK)
// ON
// smGender.ObjectTypeCode=2
// AND
// smGender.AttributeName='gendercode'
// AND
// smGender.AttributeValue=c.GenderCode
// LEFT JOIN
// StringMap AS smJob (NOLOCK)
// ON
// smJob.ObjectTypeCode=2
// AND
// smJob.AttributeName='new_jobtitle'
// AND
// smJob.AttributeValue=c.new_jobtitle
// LEFT JOIN
// StringMap AS smType (NOLOCK)
// ON
// smType.ObjectTypeCode=2
// AND
// smType.AttributeName='new_customertype'
// AND
// smType.AttributeValue=c.new_customertype
// WHERE
// c.OwningBusinessUnit='4D2ABB3A-C2B1-E411-80C7-005056A60603'";
#endregion
#region Newest Code
string sqlQuery = @"SELECT DISTINCT
c.ContactId
,c.FirstName
,c.LastName
,c.new_tcidentitynumber AS IdentityNumber
,c.MobilePhone
,c.EMailAddress1 AS EmailAddress
,smGender.Value AS Gender
,smJob.Value AS JobTitle
,c.new_countryidName AS Country
,c.new_addresscityidName AS City
,c.new_addresstownidName AS Town
,c.new_addressdistrictidName AS District
,c.new_addressdetail AS AddressDetail
,smType.Value AS CustomerType
FROM
Contact AS c (NOLOCK)
JOIN
Quote AS q (NOLOCK)
ON
q.CustomerId = c.ContactId
JOIN
new_project AS pro (NOLOCK)
ON
q.new_projectid=pro.new_projectId
JOIN
new_projectsalescollaborate AS pcol (NOLOCK)
ON
pro.new_projectId=pcol.new_projectid
JOIN
new_collaborateaccount AS col (NOLOCK)
ON
pcol.new_accountid=col.new_collaborateaccountId
LEFT JOIN
StringMap AS smGender (NOLOCK)
ON
smGender.ObjectTypeCode=2
AND
smGender.AttributeName='gendercode'
AND
smGender.AttributeValue=c.GenderCode
LEFT JOIN
StringMap AS smJob (NOLOCK)
ON
smJob.ObjectTypeCode=2
AND
smJob.AttributeName='new_jobtitle'
AND
smJob.AttributeValue=c.new_jobtitle
LEFT JOIN
StringMap AS smType (NOLOCK)
ON
smType.ObjectTypeCode=2
AND
smType.AttributeName='new_customertype'
AND
smType.AttributeValue=c.new_customertype
WHERE
pro.new_projectId!='CB9CC4EF-1117-E011-817F-00123F4DA0F7'
AND
col.new_collaborateaccountId='B3A17FFD-C5B1-E411-80C7-005056A60603'";
#endregion
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
string sqlQuery = @"SELECT
p.ProductId
,p.Name
,pro.new_projectId AS ProjectId
,pro.new_name AS ProjectIdName
,p.Price AS ListPrice
,p.new_KDVratio AS Kdv
,p.new_persquaremeter AS PerSquareMeter
,p.new_taxofstamp AS TaxOfStamp
,p.TransactionCurrencyIdName
,p.ProductNumber
,p.new_blockidName AS Block
,p.new_floornumber AS FloorNumber
,p.new_homenumber AS HomeNumber
,p.new_blockofbuildingidName AS BlockOfBuilding
,p.new_threaderidName AS Threader
,p.new_parcelidName AS Parcel
,p.new_licencenumber AS LicenceNo
,p.new_flooroflicence AS FloorOfLicence
,p.new_unittypeidName AS UnitType
,p.new_generaltypeofhomeidName AS GeneralTypeOfHome
,p.new_typeofhomeidName AS TypeOfHome
,p.new_netm2 AS Netm2
,p.new_grossm2 AS GrossM2
,p.new_balconym2 AS BalconyM2
,p.new_terracegross AS TerraceGrossM2
,p.new_kitchenm2 AS KitchenM2
,p.new_aks AS Aks
,sm.Value AS Status
,p.new_north AS DirNorth
,p.new_south AS DirSouth
,p.new_west AS DirWest
,p.new_east AS DirEast
,p.new_northwest AS DirNorthWest
,p.new_northeast AS DirNorthEast
,p.new_southwest AS DirSouthWest
,p.new_southeast AS DirSouthEast
FROM
Product AS p (NOLOCK)
JOIN
new_project AS pro (NOLOCK)
ON
p.new_projectid=pro.new_projectId
JOIN
new_projectsalescollaborate AS pcol (NOLOCK)
ON
pro.new_projectId=pcol.new_projectid
JOIN
new_collaborateaccount AS col (NOLOCK)
ON
pcol.new_accountid=col.new_collaborateaccountId
LEFT JOIN
StringMap AS sm (NOLOCK)
ON
sm.ObjectTypeCode=10016
AND
sm.AttributeName='statuscode'
AND
sm.AttributeValue=p.StatusCode
WHERE
pro.new_projectId!='CB9CC4EF-1117-E011-817F-00123F4DA0F7'
AND
col.new_collaborateaccountId='B3A17FFD-C5B1-E411-80C7-005056A60603'";
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
string sqlQuery = @"SELECT DISTINCT
c.AccountId
,c.Name
,c.new_taxnumber AS TaxNumber
,c.Telephone1
,c.EMailAddress1 AS EmailAddress
,c.new_addresscountryidName AS Country
,c.new_addresscityidName AS City
,c.new_addresstownidName AS Town
,c.new_addressdistrictidName AS District
,c.new_addressdetail AS AddressDetail
FROM
Account AS c (NOLOCK)
JOIN
Quote AS q (NOLOCK)
ON
q.CustomerId = c.AccountId
JOIN
new_project AS pro (NOLOCK)
ON
q.new_projectid=pro.new_projectId
JOIN
new_projectsalescollaborate AS pcol (NOLOCK)
ON
pro.new_projectId=pcol.new_projectid
JOIN
new_collaborateaccount AS col (NOLOCK)
ON
pcol.new_accountid=col.new_collaborateaccountId
WHERE
pro.new_projectId!='CB9CC4EF-1117-E011-817F-00123F4DA0F7'
AND
col.new_collaborateaccountId='B3A17FFD-C5B1-E411-80C7-005056A60603'" ;
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
string sqlQuery = @"SELECT
pc.ActivityId
,pc.new_activitytopicidName AS ActivityTopic
,pc.new_activitystatusName AS ActivityStatus
,pc.new_activitystatusdetailName AS ActivityStatusDetail
,pc.PhoneNumber
,pc.Description
,pc.OwnerIdName
,pc.ScheduledStart
,pc.ActualEnd
,ap.PartyIdName AS CustomerIdName
,apFrom.PartyIdName AS CallerName
,smState.Value AS State
FROM
PhoneCall AS pc (NOLOCK)
JOIN
ActivityParty AS ap (NOLOCK)
ON
ap.ActivityId=pc.ActivityId
AND
ap.ParticipationTypeMask=2 --TO
JOIN
ActivityParty AS apFrom (NOLOCK)
ON
apFrom.ActivityId=pc.ActivityId
AND
apFrom.ParticipationTypeMask=1 --FROM
JOIN
StringMap AS smState (NOLOCK)
ON
smState.ObjectTypeCode=4210
AND
smState.AttributeName='statecode'
AND
smState.AttributeValue=pc.StateCode
WHERE
pc.OwningBusinessUnit='4D2ABB3A-C2B1-E411-80C7-005056A60603'";
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
public MsCrmResult Process(SqlDataAccess sda)
{
MsCrmResult returnValue = new MsCrmResult();
#region | SQL QUERY |
string sqlQuery = @"SELECT
pay.new_paymentId AS PaymentId
,pay.new_name AS Name
,pay.TransactionCurrencyIdName
,sm.Value AS PaymentStatus
,pay.new_quoteid AS SalesId
,pay.new_quoteidName AS SalesName
,pay.new_amount AS PaidAmount
,pay.new_paymentamount AS PaymentAmount
,pay.new_balanceamount AS BalanceAmount
,pay.new_vnumber AS VoucherNo
,smVStatus.Value AS VoucherStatus
,pay.new_date AS VoucherDate
,pay.new_isvoucher AS IsVoucher
,smVType.Value AS VoucherType
,smType.Value AS PayementType
FROM
new_payment AS pay (NOLOCK)
JOIN
StringMap AS sm (NOLOCK)
ON
sm.ObjectTypeCode=10040
AND
sm.AttributeName='statuscode'
AND
sm.AttributeValue=pay.StatusCode
LEFT JOIN
StringMap AS smType (NOLOCK)
ON
smType.ObjectTypeCode=10040
AND
smType.AttributeName='new_type'
AND
smType.AttributeValue=pay.new_type
LEFT JOIN
StringMap AS smVStatus (NOLOCK)
ON
smVStatus.ObjectTypeCode=10040
AND
smVStatus.AttributeName='new_vstatus'
AND
smVStatus.AttributeValue=pay.new_vstatus
LEFT JOIN
StringMap AS smVType (NOLOCK)
ON
smVType.ObjectTypeCode=10040
AND
smVType.AttributeName='new_vtype'
AND
smVType.AttributeValue=pay.new_vtype
WHERE
pay.new_quoteid
IN
(
SELECT
q.QuoteId
FROM
Quote AS q (NOLOCK)
JOIN
Product AS p (NOLOCK)
ON
q.new_productid=p.ProductId
JOIN
new_project AS pro (NOLOCK)
ON
p.new_projectid=pro.new_projectId
JOIN
new_projectsalescollaborate AS pcol (NOLOCK)
ON
pro.new_projectId=pcol.new_projectid
JOIN
new_collaborateaccount AS col (NOLOCK)
ON
pcol.new_accountid=col.new_collaborateaccountId
WHERE
pro.new_projectId!='CB9CC4EF-1117-E011-817F-00123F4DA0F7'
AND
col.new_collaborateaccountId='B3A17FFD-C5B1-E411-80C7-005056A60603'
)
AND
pay.new_collaborateaccountid='B3A17FFD-C5B1-E411-80C7-005056A60603'" ;
#endregion
try
{
DataTable dt = sda.getDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add(dt, _dataType.ToString());
wb.SaveAs(@Environment.CurrentDirectory + @"\files\" + _dataType.ToString() + ".xlsx");
}
returnValue.Success = true;
returnValue.Result = string.Format("[{0}] adet data gönderildi.[{1}]", dt.Rows.Count.ToString(), _dataType.ToString());
}
catch (Exception ex)
{
returnValue.Result = ex.Message;
}
return(returnValue);
}
