using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CRMDAL.Query.Action
{
public class ActionQB
{
public const string GET_ACTION = @"SELECT
--TActionTable
ta. ACTIONID AS TActionId,
ta. OUID AS OrganizationUnitId,
mou.ORGANIZATIONUNITNAME AS OrganizationUnitName,
mou.ORGANIZATIONUNITCODE AS OrganizationUnitCode,
ta. PERIODID AS PeriodId,
ta. BIZTRANSACTIONTYPEID AS BIZTransactionTypeId,
mbt.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName,
mbt.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode,
ta. TRANSACTIONNUMBER AS TActionTransactionNumber,
ta. TRANSACTIONDATE AS TActionTransactionDate,
ta. REFERENCENUMBER AS TActionReferenceNumber,
ta. REFERENCEDATE AS TActionReferenceDate,
ta. ENTRYMODE AS TActionEntryMode,
ta. DEVICEID AS DeviceId,
md.DEVICENAME AS DeviceName,
md.DEVICECODE AS DeviceCode,
ta. PARTYBRANCHID AS PartyBranchId,
mpb.PARTYBRANCHNAME AS PartyBranchName,
mpb.PARTYBRANCHCODE AS PartyBranchCode,
ta. PERSONID AS PersonId,
pemp.EMPLOYEECODE AS PersonCode,
pemp.EMPLOYEENAME AS PersonName,
ta. PERIODFROM AS TActionPeriodFrom,
ta. PERIODTO AS TActionPeriodTo,
mp.PERIODNAME AS PeriodName,
mp.PERIODCODE AS PeriodCode,
mp.FROMDATE AS PeriodFromDate,
mp.TODATE AS PeriodToDate,
ta. REMARKS AS TActionRemarks,
ta. TAGS AS TActionTags,
ta. OTHERCHARGESID AS OtherChargeId,
mocg.CHARGENAME AS OtherChargeName,
mocg.CHARGECODE AS OtherChargeCode,
ta. STATUS AS TActionStatus,
ta. VERSION AS TActionVersion,
ta. CREATEDBYID AS TActionCreatedById,
ta. CREATEDON AS TActionCreatedOn,
ta. MODIFIEDBYID AS TActionModifiedById,
ta. MODIFIEDON AS TActionModifiedOn,
ta. ACTIONBYTYPE AS TActionByType,
ta. CREATEDAT AS TActionCreatedAt,
ta. MODIFIEDAT AS TActionModifiedAt,
--TActionDetail Table
tad.ACTIONDETAILID AS TActionDetailId,
tad.ACTIONID AS ActionId,
tad.SLNO AS TActionDetailSlno,
tad.ACTIONTYPE AS TActionDetailActionType,
tad.TASKID AS TaskId,
tt.TASKNUMBER AS TaskNumber,
tt.TASKNAME AS TaskName,
tt.TASKDESCRIPTION AS TaskDescription,
tad.CALLID AS CallId,
tc.CALLNUMBER AS CallNumber,
tad.BREAKDOWNID AS TActionDetailBreakdown,
tad.LEADID AS LeadId,
tl.LEADNAME AS LeadName,
tl.LEADCODE AS LeadCode,
tl.DESCRIPTION AS LeadDescription,
tad.PARTYBRANCHID AS PartyBranchId,
tadpb.PARTYBRANCHNAME AS PartyBranchName,
tadpb.PARTYBRANCHCODE AS PartyBranchCode,
tad.ACTIVITYID AS ActivityId,
mact.ACTIVITYNAME AS ActivityName,
mact.ACTIVITYCODE AS ActivityCode,
tad.ASSETID AS AssetId,
mas.ASSETNAME AS AssetName,
mas.ASSETCODE AS AssetCode,
tad.PARAMETERSETID AS ParameterSetId,
mps.PARAMETERSETNAME AS ParameterSetName,
mps.PARAMETERSETCODE AS ParameterSetCode,
tad.ACTIONSTATUS AS TActionDetailActionStatus,
CASE
WHEN tad.ACTIONSTATUS = 1 THEN 'Completed'
WHEN tad.ACTIONSTATUS = 0 THEN 'Pending'
ELSE 'Unknown'
END AS TActionDetailActionStatusName,
tad. REASONID AS ReasonId,
mr.REASONNAME AS ReasonName,
mr.REASONCODE AS ReasonCode,
tad. REMARKS AS TActionDetailRemarks,
tad. PERIODFROM AS TActionDetailPeriodFrom,
tad. PERIODTO AS TActionDetailPeriodTo,
tad. ISCOMPLETED AS TActionDetailIsCompleted,
tad. COMPLETEDON AS TActionDetailCompletedOn,
tad. DURATION AS TActionDetailDuration,
tad. ALERTS AS TActionDetailAlerts,
tad. ALLOCATIONID AS AllocationId,
mal.ALLOCATIONNAME AS AllocationName,
mal.ALLOCATIONSHORTNAME AS AllocationShortName,
tad. ATTENDEDBYTYPE AS TActionDetailAttendedByType,
tad. ATTENDEDBYEMPLOYEEID AS AttendedByEmployeeId,
tademp.EMPLOYEECODE AS AttendedByEmployeeCode,
tademp.EMPLOYEENAME AS AttendedByEmployeeName,
tad. ATTENDEDBYCONTACTID AS AttendedByContactId,
tadcon.NAME AS AttendedByContactName,
tad. ATTENDEDBYPARTYID AS AttendedByPartyId,
tadparty.PARTYCODE AS AttendedByPartyCode,
tadparty.PARTYNAME AS AttendedByPartyName,
tad. INTERVAL AS TActionDetailInterval,
tad. NETDURATION AS TActionDetailNetDuration,
tad. ISFREE AS TActionDetailIsFree,
tad. FCRATE AS TActionDetailFcRate,
tad. CURRENCYID AS CurrencyId,
mc.CURRENCYNAME AS CurrencyName,
mc.CURRENCYCODE AS CurrencyCode,
tad. CONVERSIONRATE AS TActionDetailConversionRate,
tad. RATE AS TActionDetailRate,
tad. RATEPERUOMID AS RatePerUOMId,
tad. FCAMOUNT AS TActionDetailFcAmount,
tad. AMOUNT AS TActionDetailAmount,
tad. AMOUNTPLUS AS TActionDetailAmountPlus,
tad. AMOUNTMINUS AS TActionDetailAmountMinus,
tad. GROSSAMOUNT AS TActionDetailGrossAmount,
tad. OTHERAMOUNTPLUS AS TActionDetailOtherAmountPlus,
tad. OTHERAMOUNTMINUS AS TActionDetailOtherAmountMinus,
tad. NETAMOUNT AS TActionDetailNetAmount,
tad. ISMACHINERUNNING AS TActionDetailIsMachineRunning,
tad. ISINVOICED AS TActionDetailIsInvoiced,
tad. CHARGESID AS ChargeId,
mcg.CHARGENAME AS ChargeName,
mcg.CHARGECODE AS ChargeCode,
tad. FOLLOWUPTASKID AS FollowUpTaskId,
tf. TASKNUMBER AS FollowUpTaskNumber,
tad. PENDINGREASONID AS PendingReasonId,
mrp.REASONNAME AS PendingReasonName,
mrp.REASONCODE AS PendingReasonCode,
tad. completedPercentage AS TActionDetailCompletedPercentage,
tad. ACTIONDETAILSTATUS AS TActionDetailActionDetailStatus,
tad. PRIORITYID AS PriorityId,
mgc.GCMNAME AS PriorityName,
mgc.GCMCODE AS PriorityCode,
tad. EXPECTEDDATE AS TActionDetailExpectedDate,
tad. EXPECTEDFCREVENUE AS TActionDetailExpectedFCRevenue,
tad. ORDERCONFIDENCE AS TActionDetailOrderConfidence,
tad. REVENUECONFIDENCE AS TActionDetailRevenueConfidence,
tad. SUCCESSSTATUS AS TActionDetailSuccessStatus,
tad. LEADCYCLEDETAILID AS LeadCycleDetailId,
mld.STAGENAME AS LeadCycleDetailStageName,
mld.STAGECODE AS LeadCycleDetailStageCode,
tad. QUANTITY AS TActionDetailQuantity,
tad. NOOFDATA AS TActionDetailNoofData,
tad. OBJECTTYPEID AS ObjectTypeId,
me.ENTITYNAME AS ObjectTypeName,
me.ENTITYCODE AS ObjectTypeCode,
tad. OBJECTID AS ObjectId,
tad. WORKTYPEDETAILID AS WorkTypeDetailId,
tad. SELECTIONREMARKS AS TActionDetailSelectionRemarks,
tad. SELECTIONSTATUS AS TActionDetailSelectionStatus,
tad. EXPENSEHEADID AS ExpenseHeadId,
ma.ACTIVITYNAME AS ExpenseHeadName,
ma.ACTIVITYCODE AS ExpenseHeadCode,
tad. PAIDTOACCOUNTID AS PaidToAccountId,
mac.ACCOUNTNAME AS PaidToAccountName,
mac.ACCOUNTCODE AS PaidToAccountCode,
tad. CLAIMAMOUNT AS TActionDetailClaimAmount,
tad. APPROVEDAMOUNT AS TActionDetailApprovedAmount,
tad. RECOVERYPERCENTAGE AS TActionDetailRecoveryPercentage,
tad. RECOVERYVALUE AS TActionDetailRecoveryValue,
tad. RECOVERYTYPE AS TActionDetailRecoveryType,
tad. ATTENDEDBYUSERID AS AttendedByUserId,
mu.USERNAME AS AttendedByUserName,
mu.USERCODE AS AttendedByUserCode,
--TActionItems Table
tai.ACTIONITEMID AS TActionItemsId,
tai.ACTIONDETAILID AS TActionDetailId,
tai.ACTIONID AS TActionId,
tai.SLNO AS TActionItemsSlNo,
tai.TYPE AS TActionItemsType,
tai.ITEMID AS ItemId,
tai.SKUID AS SKUId,
tai.UOMID AS UomId,
tai.GOODQUANTITY AS TActionItemsGoodQuantity,
tai.REJECTEDQUANTITY AS TActionItemsRejectedQuantity,
tai.REWORKQUANTITY AS TActionItemsReworkQuantity,
tai.OTHERQUANTITY AS TActionItemsOtherQuantity,
tai.PACKQUANTITY AS TActionItemsPackQuantity,
tai.STOCKPOSTTYPE AS TActionItemsStockPostType,
tai.ISFREE AS TActionItemsIsFree,
tai.RATE AS TActionItemsRate,
tai.POSTTOMACHINEHISTORY AS TActionItemsPostToMachineHistory,
tai.REMARKS AS TActionItemsRemarks,
tai.CHARGETYPE AS TActionItemsChargeType,
mitem.ITEMCODE AS ItemCode,
mitem.ITEMNAME AS ItemName,
msku.SKUNAME AS SKUName,
msku.SKUCODE AS SKUCode,
muom.UOMCODE AS UomCode,
muom.UOMNAME AS UomName
FROM TAction ta
LEFT JOIN TActionDetail tad ON ta.ACTIONID = tad.ACTIONID
LEFT JOIN TActionItems tai ON tad.ACTIONDETAILID = tai.ACTIONDETAILID
LEFT JOIN MORGANIZATIONUNIT mou ON ta.OUID = mou.OUID
LEFT JOIN MBIZTRANSACTIONTYPE mbt ON ta.BIZTRANSACTIONTYPEID = mbt.BIZTRANSACTIONTYPEID
LEFT JOIN MDEVICE md ON ta.DEVICEID = md.DEVICEID
LEFT JOIN MPARTYBRANCH mpb ON ta.PARTYBRANCHID = mpb.PARTYBRANCHID
LEFT JOIN MPARTYBRANCH tadpb ON tad.PARTYBRANCHID = tadpb.PARTYBRANCHID
LEFT JOIN MPERIOD mp ON ta.PERIODID = mp.PERIODID
LEFT JOIN MEMPLOYEE pemp ON ta.PERSONID = pemp.EMPLOYEEID
LEFT JOIN MCHARGE mocg ON ta.OTHERCHARGESID = mocg.CHARGEID
LEFT JOIN TTASK tt ON tad.TASKID = tt.TASKID
LEFT JOIN TCALL tc ON tad.CALLID = tc.CALLID
LEFT JOIN MACTIVITY mact ON tad.ACTIVITYID = mact.ACTIVITYID
LEFT JOIN MASSET mas ON tad.ASSETID = mas.ASSETID
LEFT JOIN MPARAMETERSET mps ON tad.PARAMETERSETID = mps.PARAMETERSETID
LEFT JOIN MREASON mr ON tad.REASONID = mr.REASONID
LEFT JOIN MALLOCATION mal ON tad.ALLOCATIONID = mal.ALLOCATIONID
LEFT JOIN MCURRENCY mc ON tad.CURRENCYID = mc.CURRENCYID
LEFT JOIN MUSER mu ON tad.ATTENDEDBYUSERID = mu.USERID
LEFT JOIN MEMPLOYEE tademp ON tad.ATTENDEDBYEMPLOYEEID = tademp.EMPLOYEEID
LEFT JOIN MCONTACT tadcon ON tad.ATTENDEDBYCONTACTID = tadcon.CONTACTID
LEFT JOIN MPARTY tadparty ON tad.ATTENDEDBYPARTYID = tadparty.PARTYID
LEFT JOIN MCHARGE mcg ON tad.CHARGESID = mcg.CHARGEID
LEFT JOIN TTASK tf ON tad.FOLLOWUPTASKID = tf.TASKID
LEFT JOIN MREASON mrp ON tad.PENDINGREASONID = mrp.REASONID
LEFT JOIN TLEAD tl ON tad.LEADID = tl.LEADID
LEFT JOIN MGCM mgc ON tad.PRIORITYID = mgc.GCMID
LEFT JOIN MLEADCYCLEDETAIL mld ON tad.LEADCYCLEDETAILID = mld.LEADCYCLEDETAILID
LEFT JOIN MENTITY me ON tad.OBJECTTYPEID = me.ENTITYID
LEFT JOIN MACTIVITY ma ON tad.EXPENSEHEADID = ma.ACTIVITYID
LEFT JOIN MACCOUNT mac ON tad.PAIDTOACCOUNTID = mac.ACCOUNTID
LEFT JOIN MITEM mitem ON tai.ITEMID = mitem.ITEMID
LEFT JOIN MSKU msku ON tai.SKUID = msku.SKUID
LEFT JOIN MUOM muom ON tai.UOMID = muom.UOMID
WHERE ta.ACTIONID = @TActionId
ORDER BY ta.ACTIONID, tad.ACTIONDETAILID, tai.ACTIONITEMID;";
public const string GET_SELECTLIST_ACTION = @"
WITH ActionCTE AS (
SELECT ta.ACTIONID AS Id,
ta.TRANSACTIONNUMBER AS TransactionNumber,
ta.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
ta.OUID AS OuId,
ta.PERSONID AS PersonId,
ta.STATUS AS Status,
ta.TRANSACTIONDATE AS TransactionDate,
ta.PERIODFROM AS PeriodFrom,
ta.PERIODTO AS PeriodTo,
ta.REFERENCENUMBER AS ReferenceNumber,
ta.ENTRYMODE AS EntryMode,
ta.ACTIONBYTYPE AS ActionByType,
ROW_NUMBER() OVER (ORDER BY ta.ACTIONID) AS RowNum
FROM TACTION ta
WHERE 1=1 /*CRITERIA*/)
SELECT Id, TransactionNumber
FROM ActionCTE
WHERE (@firstnumber = -1 AND @maxresult = -1)
OR (RowNum BETWEEN @firstnumber AND @maxresult);";
public const string GET_SELECTLIST_ACTION_BASE = @"
SELECT ta.ACTIONID AS Id,
ta.TRANSACTIONNUMBER AS TransactionNumber
FROM TACTION ta
WHERE 1=1";
public const string LEAD_TEMP_ALLOTEDEMPLOYEE = @"
SELECT ld.LEADID, allt.EMPLOYEENAME, allt.EMPLOYEEID
INTO #allotedtemp
FROM TLEAD ld
OUTER APPLY (
SELECT * FROM MEMPLOYEE
WHERE EMPLOYEEID IN (
SELECT value FROM STRING_SPLIT(CAST(ld.ALLOTTEDTOIDS AS NVARCHAR(MAX)), ',')
)
) allt,
TACTIONDETAIL ACTDETIL
WHERE 1=1
AND ACTDETIL.LEADID = ld.LEADID";
public const string GET_ACTIVITY_REGISTER = @"
SELECT
EMP.EMPLOYEEID AS PersonId,
EMP.EMPLOYEECODE AS PersonCode,
EMP.EMPLOYEENAME AS PersonName,
EMP.FIRSTNAME AS Name,
dept.DEPARTMENTCODE AS DepartmentCode,
dept.DEPARTMENTNAME AS DepartmentName,
activity.ISPRODUCTIVE AS IsProductive,
(TotEmployee.NumberOfOrders) AS TotalNumberofEmployees,
(NDEmployee.NumberOfOrders) AS NonDREmployees,
((TotEmployee.NumberOfOrders)-(DRSub.DRsubmitted+LeaveEmp.LeaveEmployee+NDEmployee.NumberOfOrders)) AS Missed,
DRSub.DRsubmitted AS DRsubmitted,
np.NotPunched AS NotPunch,
LeaveEmp.LeaveEmployee AS LeaveEmployees,
ACTION.PERIODFROM AS TActionTransactionDate,
ACTION.VERSION AS TActionVersion,
CONVERT(CHAR(5), ACTDETIL.PERIODFROM, 108) AS TActionDetailPeriodFrom,
CONVERT(CHAR(5), ACTDETIL.PERIODTO, 108) AS TActionDetailPeriodTo,
CONVERT(NUMERIC(18,2), ACTDETIL.DURATION/60 + (ACTDETIL.DURATION%60)/60.0) AS TActionDetailDuration,
ACTDETIL.DURATION AS Duration,
CASE ACTDETIL.ACTIONTYPE
WHEN 0 THEN 'TASK' WHEN 1 THEN 'ACTIVITY'
WHEN 2 THEN 'CALL' WHEN 3 THEN 'BREAKDOWN' WHEN 4 THEN 'LEAD'
END AS TActionDetailActionType,
ACTDETIL.ACTIONID AS ActionId,
CASE ACTDETIL.ACTIONTYPE
WHEN 0 THEN TASK.TASKNUMBER WHEN 1 THEN ACT.ACTIVITYCODE
WHEN 2 THEN CALL.CALLNUMBER WHEN 3 THEN ''
WHEN 4 THEN lead.LEADCODE
END AS Number,
CASE ACTDETIL.ACTIONTYPE
WHEN 0 THEN CAST(TASK.TASKDESCRIPTION AS NVARCHAR(4000))
WHEN 1 THEN ACT.ACTIVITYNAME
WHEN 2 THEN CAST(CALL.REPORTEDPROBLEM AS NVARCHAR(4000))
WHEN 3 THEN '' WHEN 4 THEN lead.DESCRIPTION
END AS Description,
CAST(ACTDETIL.REMARKS AS NVARCHAR(4000)) AS TActionDetailRemarks,
CASE ACTDETIL.ACTIONSTATUS
WHEN 0 THEN 'PENDING' WHEN 1 THEN 'Completed'
END AS TActionDetailActionStatus,
REASON.REASONNAME AS ReasonName,
CONVERT(NUMERIC(18,2), TASK.PLANNEDHOURS/60 + (TASK.PLANNEDHOURS%60)/100.0) AS PlannedHours,
CONVERT(NUMERIC(18,2), TASK.ACTUALHOURS/60 + (TASK.ACTUALHOURS%60)/100.0) AS ActualHours,
ALLOCATION.ALLOCATIONID AS ProjectId,
ALLOCATION.ALLOCATIONNAME AS ProjectName,
ALLOCATION.DESCRIPTION AS ProjectShortName,
ACTDETIL.COMPLETEDPERCENTAGE AS CompletedPercentage,
0 AS Type,
(TASK.PLANNEDHOURS)*1/100.0*((ACTDETIL.COMPLETEDPERCENTAGE)-(TaskOpening.openpercent)) AS MovementDuration,
ROUND((ACTDETIL.DURATION/480.0)*100,2) AS UtilizationPercent,
CASE ACTDETIL.DURATION WHEN 0 THEN 0 ELSE
ROUND(((TASK.PLANNEDHOURS)*1/100.0*((ACTDETIL.COMPLETEDPERCENTAGE)-(TaskOpening.openpercent)))/(ACTDETIL.DURATION)*100,2)
END AS EfficiencyPercent,
CASE WHEN activity.ISPRODUCTIVE=0 THEN
((TASK.PLANNEDHOURS)*1/100.0*((ACTDETIL.COMPLETEDPERCENTAGE)-(TaskOpening.openpercent))/480*100) ELSE 0
END AS ProductivePercent,
CASE WHEN activity.ISPRODUCTIVE=0 THEN
(TASK.PLANNEDHOURS)*1/100.0*((ACTDETIL.COMPLETEDPERCENTAGE)-(TaskOpening.openpercent)) ELSE 0
END AS ProductiveDuration,
lead.LEADID AS LeadId,
lead.LEADCODE AS LeadCode,
lead.LEADNAME AS LeadName,
ftask.TASKDATE AS FollowupTaskDate,
CONVERT(CHAR(5), ftask.TASKDATE, 108) AS FTaskTime,
ftaskincharge.EMPLOYEENAME AS FTaskIncharge,
ftaskactivity.ACTIVITYNAME AS FTaskActivity,
ftask.TASKNAME AS FTaskName,
leadcontact.MOBILENO AS LeadContactMobileNo,
leadcontact.NAME AS LeadContactName,
leadcontact.COMPANY AS LeadContactCompany,
leadcontact.DESIGNATION AS LeadDesignation,
leadcontact.PHONENO AS LeadPhoneNo,
madd.ZIPCODE AS PinCode,
stat.STATECODE AS StateCode,
stat.STATENAME AS StateName,
leadcontact.CONTACTID AS ContactId,
contact.LASTNAME AS ContactLastName,
contact.MIDDLENAME AS ContactMiddleName,
contact.NAME AS ContactName,
contact.FIRSTNAME AS ContactFirstName,
contact.DESIGNATION AS ContactDesignation,
contact.MOBILENO AS ContactMobNo,
contact.MAILID AS ContactMailId,
contact.COMPANY AS ContactCompany,
madd.WEB AS Web,
leadcontact.MAILID AS Mail,
city.CITYID AS CityId,
city.CITYCODE AS CityCode,
city.CITYNAME AS CityName,
allt.AllotedEmployee AS AllotedEmployeeName,
attparty.PARTYCODE AS CustomerCode,
attparty.PARTYNAME AS CustomerName,
Designation.GCMNAME AS Designation,
ACTDETIL.NOOFDATA AS NoOfData,
ACTDETIL.QUANTITY AS Quantity,
TASK.BUDGETCOST AS BudgetCost,
TASK.BUDGETREVENUE AS BudgetRevenue,
TASK.ACTUALCOST AS ActualCost,
TASK.REFERENCENUMBER AS TaskReferenceNumber,
TASK.ALLOCATIONID AS AllocationId,
alloc.ALLOCATIONNAME AS Allocation,
alloc.ALLOCATIONSHORTNAME AS AllocationShortName,
EMP.REPORTINGTOEMPLOYEEID AS ReportingToId,
rep.EMPLOYEECODE AS ReportingToCode,
rep.EMPLOYEENAME AS ReportingToName,
rep.PRIMARYEMAILID AS ReportingToMailId,
ACTDETIL.TASKID AS TaskId,
ISNULL(ACTION.TRANSACTIONNUMBER,'') AS ActionNumber,
ISNULL(Actdtlactivity.ACTIVITYNAME,'') AS ActionActivity,
ISNULL(ACTION.REMARKS,'') AS ActionRemarks
FROM TTASK TASK, MALLOCATION alloc, TACTION ACTION, TACTIONDETAIL ACTDETIL
CROSS APPLY (
SELECT a.TASKID, ISNULL(MAX(c.COMPLETEDPERCENTAGE),0) openpercent
FROM TTASK a
LEFT OUTER JOIN TACTIONDETAIL c ON (a.TASKID=c.TASKID AND c.PERIODTO < ACTDETIL.PERIODFROM)
WHERE a.TASKID = ACTDETIL.TASKID
GROUP BY a.TASKID
) TaskOpening,
TLEAD lead
-- STRING_SPLIT needs compat level 130+; GB5 must still run against compat-100
-- tenant databases (confirmed on UNISOFTGB5), so ALLOTTEDTOIDS is split with the
-- XML .nodes() technique instead, which works from SQL Server 2005/compat 90 up.
OUTER APPLY (
SELECT STUFF((
SELECT ' ' + e2.EMPLOYEENAME
FROM MEMPLOYEE e2
WHERE e2.EMPLOYEEID IN (
SELECT LTRIM(RTRIM(x.i.value('.', 'NVARCHAR(50)')))
FROM (
SELECT CAST('' + REPLACE(CAST(lead.ALLOTTEDTOIDS AS NVARCHAR(MAX)), ',', '') + '' AS XML) AS ids
) AS split
CROSS APPLY split.ids.nodes('/i') AS x(i)
)
FOR XML PATH('')
), 1, 1, '') AS AllotedEmployee
) allt,
MCONTACT leadcontact
LEFT OUTER JOIN MADDRESS madd ON (leadcontact.CONTACTID=madd.OBJECTID AND madd.OBJECTTYPEID=-2147481759)
LEFT OUTER JOIN MCITY city ON (city.CITYID=madd.CITYID)
LEFT OUTER JOIN MSTATE stat ON (stat.STATEID=madd.STATEID)
LEFT OUTER JOIN MREGION rg ON (madd.STANDARDREGIONID=rg.REGIONID),
MCONTACT contact,
TTASK ftask,
MEMPLOYEE ftaskincharge,
MACTIVITY ftaskactivity,
MREASON REASON,
MEMPLOYEE EMP, MDEPARTMENT dept, MACTIVITY ACT, MORGANIZATIONUNIT OU,
TCALL CALL, MEMPLOYEE INCHARGE, MASSET mas,
MEMPLOYEE rep,
MPARTY PARTY, MALLOCATION ALLOCATION, MPARTYBRANCH PARTYBRANCH, TTASK PARENTTASK,
MACTIVITY activity,
(SELECT COUNT(EMPLOYEESTATUS) AS NumberOfOrders FROM MEMPLOYEE WHERE EMPLOYEESTATUS=0 AND STATUS=1 AND EMPLOYEEID<>-1 {replacefilters}) TotEmployee,
(SELECT COUNT(EMPLOYEESTATUS) AS NumberOfOrders FROM MEMPLOYEE WHERE EMPLOYEESTATUS=0 AND STATUS=1 AND EMPLOYEEID<>-1 AND ISDAILYREPORT=1) NDEmployee,
(SELECT COUNT(*) AS DRsubmitted FROM TACTION, MEMPLOYEE WHERE TRANSACTIONDATE='{periodfromdate}' AND TACTION.PERSONID=MEMPLOYEE.EMPLOYEEID AND MEMPLOYEE.EMPLOYEESTATUS=0 AND MEMPLOYEE.STATUS=1 {replacefilters}) DRSub,
(SELECT COUNT(*) AS LeaveEmployee FROM TDAILYATTENDANCE, MEMPLOYEE WHERE ATTENDANCEDATE='{periodfromdate}' AND DAYTYPE IN(4,8) AND MEMPLOYEE.EMPLOYEEID=TDAILYATTENDANCE.EMPLOYEEID AND MEMPLOYEE.EMPLOYEESTATUS=0 AND MEMPLOYEE.STATUS=1 {replacefilters}) LeaveEmp,
(SELECT COUNT(*) AS NotPunched FROM MEMPLOYEE
WHERE EMPLOYEEID NOT IN (SELECT DISTINCT EMPLOYEEID FROM TPUNCH WHERE PUNCHDATE='{periodfromdate}')
AND EMPLOYEEID NOT IN (SELECT DISTINCT EMPLOYEEID FROM TDAILYATTENDANCE WHERE ATTENDANCEDATE='{periodfromdate}' AND DAYTYPE IN(4,8))
AND MEMPLOYEE.EMPLOYEESTATUS=0 AND MEMPLOYEE.STATUS=1 AND ISDAILYREPORT=0) np,
MPARTY attparty, MGCM Designation, MACTIVITY Actdtlactivity
WHERE 1=1
AND ACTION.ACTIONID=ACTDETIL.ACTIONID
AND ACTION.OUID=OU.OUID
AND ACTDETIL.TASKID=TASK.TASKID
AND TASK.ALLOCATIONID=alloc.ALLOCATIONID
AND PARENTTASK.TASKID=TASK.PARENTTASKID
AND ACTION.PERSONID=EMP.EMPLOYEEID
AND EMP.DEPARTMENTID=dept.DEPARTMENTID
AND EMP.REPORTINGTOEMPLOYEEID=rep.EMPLOYEEID
AND ACTDETIL.REASONID=REASON.REASONID
AND ACTDETIL.ACTIVITYID=ACT.ACTIVITYID
AND TASK.ACTIVITYID=activity.ACTIVITYID
AND ACTDETIL.CALLID=CALL.CALLID
AND TASK.PARTYBRANCHID=PARTYBRANCH.PARTYBRANCHID
AND PARTYBRANCH.PARTYID=PARTY.PARTYID
AND TASK.INCHARGEID=INCHARGE.EMPLOYEEID
AND TASK.ALLOCATIONID=ALLOCATION.ALLOCATIONID
AND ACTDETIL.ATTENDEDBYCONTACTID=contact.CONTACTID
AND ACTDETIL.FOLLOWUPTASKID=ftask.TASKID
AND ftask.INCHARGEID=ftaskincharge.EMPLOYEEID
AND ACTDETIL.LEADID=lead.LEADID
AND mas.ASSETID=ACTDETIL.ASSETID
AND leadcontact.CONTACTID=lead.CONTACTID
AND ALLOCATION.PARTYID=attparty.PARTYID
AND EMP.DESIGNATIONID=Designation.GCMID
AND ftask.ACTIVITYID=ftaskactivity.ACTIVITYID
AND ACTDETIL.ACTIVITYID=Actdtlactivity.ACTIVITYID";
public const string GET_ACTIVITY_REGISTER_TOTAL = @"
SELECT
SUM(CASE ACTDETIL.ACTIONTYPE WHEN 0 THEN ACTDETIL.DURATION ELSE 0 END) AS TotalTask,
SUM(CASE ACTDETIL.ACTIONTYPE WHEN 1 THEN ACTDETIL.DURATION ELSE 0 END) AS TotalActivity,
SUM(CASE activity.ISPRODUCTIVE WHEN 0 THEN ACTDETIL.DURATION ELSE 0 END) AS TotalProductive,
SUM(CASE ALLOCATION.ALLOCATIONID WHEN -1 THEN 0 ELSE ACTDETIL.DURATION END) AS TotalAllocation
FROM TTASK TASK, TACTION ACTION, TACTIONDETAIL ACTDETIL,
TLEAD lead,
MCONTACT leadcontact
LEFT OUTER JOIN MADDRESS madd ON (leadcontact.CONTACTID=madd.OBJECTID AND madd.OBJECTTYPEID=-2147481759)
LEFT OUTER JOIN MCITY city ON (city.CITYID=madd.CITYID)
LEFT OUTER JOIN MREGION rg ON (madd.STANDARDREGIONID=rg.REGIONID),
MCONTACT contact,
TTASK ftask, MEMPLOYEE ftaskincharge, MACTIVITY ftaskactivity,
MREASON REASON,
MEMPLOYEE EMP, MDEPARTMENT dept, MACTIVITY ACT, MORGANIZATIONUNIT OU,
TCALL CALL, MEMPLOYEE INCHARGE, MASSET mas,
MPARTY PARTY, MALLOCATION ALLOCATION, MPARTYBRANCH PARTYBRANCH, TTASK PARENTTASK,
MACTIVITY activity,
MPARTY attparty, MGCM Designation
WHERE 1=1
AND ACTION.ACTIONID=ACTDETIL.ACTIONID
AND ACTION.OUID=OU.OUID
AND ACTDETIL.TASKID=TASK.TASKID
AND PARENTTASK.TASKID=TASK.PARENTTASKID
AND ACTION.PERSONID=EMP.EMPLOYEEID
AND EMP.DEPARTMENTID=dept.DEPARTMENTID
AND ACTDETIL.REASONID=REASON.REASONID
AND ACTDETIL.ACTIVITYID=ACT.ACTIVITYID
AND TASK.ACTIVITYID=activity.ACTIVITYID
AND ACTDETIL.CALLID=CALL.CALLID
AND TASK.PARTYBRANCHID=PARTYBRANCH.PARTYBRANCHID
AND PARTYBRANCH.PARTYID=PARTY.PARTYID
AND TASK.INCHARGEID=INCHARGE.EMPLOYEEID
AND TASK.ALLOCATIONID=ALLOCATION.ALLOCATIONID
AND ACTDETIL.ATTENDEDBYCONTACTID=contact.CONTACTID
AND ACTDETIL.FOLLOWUPTASKID=ftask.TASKID
AND ftask.INCHARGEID=ftaskincharge.EMPLOYEEID
AND ACTDETIL.LEADID=lead.LEADID
AND leadcontact.CONTACTID=lead.CONTACTID
AND mas.ASSETID=ACTDETIL.ASSETID
AND ALLOCATION.PARTYID=attparty.PARTYID
AND EMP.DESIGNATIONID=Designation.GCMID
AND ftask.ACTIVITYID=ftaskactivity.ACTIVITYID";
public const string GET_DRPRESENT = @"
SELECT EMPLOYEECODE AS EmployeeCode, EMPLOYEENAME AS EmployeeName, 1 AS Type
FROM MEMPLOYEE
WHERE EMPLOYEEID NOT IN (SELECT DISTINCT PERSONID FROM TACTION WHERE TRANSACTIONDATE=@periodfromdate)
AND EMPLOYEEID NOT IN (SELECT DISTINCT EMPLOYEEID FROM TDAILYATTENDANCE WHERE ATTENDANCEDATE=@periodfromdate AND DAYTYPE IN(4,8))
AND EMPLOYEEID IN (SELECT DISTINCT EMPLOYEEID FROM TPUNCH WHERE PUNCHDATE=@periodfromdate)
AND STATUS=1 AND EMPLOYEESTATUS=0 AND ISDAILYREPORT=0 AND EMPLOYEEID<>-1";
public const string GET_DRLEAVE = @"
SELECT b.EMPLOYEECODE AS EmployeeCode, b.EMPLOYEENAME AS EmployeeName, 2 AS Type
FROM TDAILYATTENDANCE a, MEMPLOYEE b
WHERE ATTENDANCEDATE=@periodfromdate AND DAYTYPE IN(4,8,5)
AND b.STATUS=1 AND b.EMPLOYEESTATUS=0 AND a.EMPLOYEEID=b.EMPLOYEEID";
public const string GET_DAILYREPORT_NO = @"
SELECT EMPLOYEECODE AS EmployeeCode, EMPLOYEENAME AS EmployeeName, 3 AS Type
FROM MEMPLOYEE
WHERE STATUS=1 AND EMPLOYEESTATUS=0 AND ISDAILYREPORT=1 AND EMPLOYEEID<>-1";
public const string GET_NOT_PUNCH_LIST = @"
SELECT EMPLOYEECODE AS EmployeeCode, EMPLOYEENAME AS EmployeeName, 4 AS Type
FROM MEMPLOYEE
WHERE EMPLOYEEID NOT IN (SELECT DISTINCT PERSONID FROM TACTION WHERE TRANSACTIONDATE=@periodfromdate)
AND EMPLOYEEID NOT IN (SELECT DISTINCT EMPLOYEEID FROM TDAILYATTENDANCE WHERE ATTENDANCEDATE=@periodfromdate AND DAYTYPE IN(4,8))
AND EMPLOYEEID NOT IN (SELECT DISTINCT EMPLOYEEID FROM TPUNCH WHERE PUNCHDATE=@periodfromdate)
AND STATUS=1 AND EMPLOYEESTATUS=0 AND ISDAILYREPORT=0 AND EMPLOYEEID<>-1";
public const string GET_READING_ENTRY_REPORT_CTE = @"
WITH temp AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY e.ASSETID, e.ASSETCODE, e.ASSETNAME ORDER BY e.ASSETID, e.ASSETCODE, e.ASSETNAME, c.PERIODFROM) AS SlNo,
CAST(action.TRANSACTIONDATE AS DATE) AS TransactionDate,
CAST(c.PERIODFROM AS DATE) AS ActionDetailDate,
a.READINGSID AS ReadingId,
a.PARAMETERID AS ParameterId,
b.PARAMETERCODE AS ParameterCode,
b.PARAMETERNAME AS ParameterName,
b.MINVALUE AS Minimum,
b.MAXVALUE AS Maximum,
a.FINALVALUE AS Final,
CONVERT(VARCHAR(10), d.PLANSTARTON, 108) AS Time,
e.ASSETID AS AssetId,
e.ASSETCODE AS AssetCode,
e.ASSETNAME AS AssetName,
e.ASSETBATCH AS AssetBatch,
type.ASSETTYPECODE AS AssetTypeCode,
type.ASSETTYPENAME AS AssetTypeName,
mc.ACTIVITYID AS ActivityId,
mc.ACTIVITYNAME AS ActivityName,
input.GCMNAME AS InputName,
a.INPUTTEXT AS InputText,
a.INPUTVALUE AS InputValue,
emp.EMPLOYEENAME AS EmployeeName,
emp.EMPLOYEECODE AS EmployeeCode,
s.SHIFTCODE AS ShiftName,
mp.PARTYBRANCHNAME AS PartyBranchName,
ma.ALLOCATIONNAME AS AllocationName,
CASE WHEN a.INPUTID=-1 THEN CONVERT(VARCHAR(200),a.FINALVALUE)
WHEN a.INPUTID<>-1 THEN lov.LOVNAME END AS LOVStatus,
pb.PARTYBRANCHCODE AS AssetPartyBranchCode,
pb.PARTYBRANCHNAME AS AssetPartyBranchName
FROM TREADINGS a, MPARAMETER b, TACTIONDETAIL c, TTASK d, MASSET e,
MACTIVITY mc, MGCM input, MEMPLOYEE emp, MSHIFT s, MPARTYBRANCH mp,
MALLOCATION ma, TACTION action, MASSETTYPE type, MLOV lov, MPARTYBRANCH pb
WHERE 1=1
AND a.ACTIONDETAILID=c.ACTIONDETAILID
AND a.PARAMETERID=b.PARAMETERID
AND c.TASKID=d.TASKID
AND c.ASSETID=e.ASSETID
AND c.ACTIVITYID=mc.ACTIVITYID
AND a.INPUTID=input.GCMID
AND c.ATTENDEDBYEMPLOYEEID=emp.EMPLOYEEID
AND emp.SHIFTID=s.SHIFTID
AND c.PARTYBRANCHID=mp.PARTYBRANCHID
AND d.ALLOCATIONID=ma.ALLOCATIONID
AND e.ASSETTYPEID=type.ASSETTYPEID
AND (a.FINALVALUE<>0.0 OR CONVERT(VARCHAR(10),d.PLANSTARTON,108)<>'00:00:00')
AND action.ACTIONID=c.ACTIONID
AND a.INPUTID=lov.LOVID
AND e.PARTYBRANCHID=pb.PARTYBRANCHID
),
temp2 AS (
SELECT
a.ASSETID, a.ASSETCODE, a.ASSETNAME, a.ASSETBATCH,
a.ASSETTYPECODE, a.ASSETTYPENAME, a.ACTIONDETAILDATE, a.TRANSACTIONDATE,
a.ACTIVITYID, a.ACTIVITYNAME, a.INPUTNAME, a.INPUTTEXT,
a.EMPLOYEECODE, a.EMPLOYEENAME, a.SHIFTNAME, a.PARTYBRANCHNAME, a.ALLOCATIONNAME,
a.PARAMETERCODE, a.PARAMETERNAME, a.Time, a.LOVSTATUS,
a.ASSETPARTYBRANCHCODE, a.ASSETPARTYBRANCHNAME,
ROW_NUMBER() OVER (PARTITION BY a.ASSETID ORDER BY a.ASSETID, a.ASSETCODE, a.ASSETNAME, a.ACTIONDETAILDATE) AS SlNo,
MIN(Final) AS CFromTime, MAX(Final) AS CToTime
FROM temp a
GROUP BY a.ASSETID, a.ASSETCODE, a.ASSETNAME, a.ASSETBATCH, a.ASSETTYPECODE,
a.ASSETTYPENAME, a.TRANSACTIONDATE, a.ACTIONDETAILDATE, a.ACTIVITYID,
a.ACTIVITYNAME, a.INPUTNAME, a.INPUTTEXT, a.EMPLOYEENAME, a.EMPLOYEECODE,
a.SHIFTNAME, a.PARTYBRANCHNAME, a.ALLOCATIONNAME, a.PARAMETERCODE,
a.PARAMETERNAME, a.Time, a.LOVSTATUS, a.ASSETPARTYBRANCHCODE, a.ASSETPARTYBRANCHNAME
),
temp3 AS (
SELECT a.*,
CASE WHEN a.SlNo=1 THEN a.CFromTime ELSE b.CToTime END AS FromTime,
a.CToTime AS ToTime,
a.CToTime - CASE WHEN a.SlNo=1 THEN a.CFromTime ELSE b.CToTime END AS Difference
FROM temp2 a
LEFT OUTER JOIN temp2 b ON (a.ASSETID=b.ASSETID AND b.SlNo=a.SlNo-1)
)
SELECT a.*,
ISNULL(b.diff, 0) AS RunningTotal
FROM temp3 a
OUTER APPLY (
SELECT SUM(Difference) diff FROM temp3 cc
WHERE cc.SlNo<=a.SlNo AND cc.ASSETID=a.ASSETID
) b";
public const string GET_READING_ENTRY_REPORT_COUNT = @"
SELECT COUNT(*) AS NumberOfRecords
FROM TREADINGS a, MPARAMETER b, TACTIONDETAIL c, TTASK d, MASSET e,
MACTIVITY mc, TACTION action, MASSETTYPE type, MLOV lov, MPARTYBRANCH pb
WHERE 1=1
AND a.ACTIONDETAILID=c.ACTIONDETAILID
AND a.PARAMETERID=b.PARAMETERID
AND c.TASKID=d.TASKID
AND d.ASSETID=e.ASSETID
AND c.ACTIVITYID=mc.ACTIVITYID
AND action.ACTIONID=c.ACTIONID
AND e.ASSETTYPEID=type.ASSETTYPEID
AND (a.FINALVALUE<>0.0 OR CONVERT(VARCHAR(10),d.PLANSTARTON,108)<>'00:00:00')
AND a.INPUTID=lov.LOVID
AND e.PARTYBRANCHID=pb.PARTYBRANCHID";
public const string UPDATE_ACTION_BASED_ON_SELECTION = @"
UPDATE TACTION
SET TRANSACTIONDATE=@transactiondate,
REMARKS=@particulars
WHERE ACTIONID=@actionid";
public const string UPDATE_ACTION_DETAIL_BASED_ON_SELECTION = @"
UPDATE TACTIONDETAIL
SET ACTIVITYID=@activityid,
ACTIONSTATUS=CASE @actionstatus WHEN 'Pending' THEN 0 ELSE 1 END,
SUCCESSSTATUS=CASE @actionstatus
WHEN 'Shortlisted' THEN 5 WHEN 'Rejected' THEN 6
WHEN 'Selected' THEN 7 WHEN 'Accepted' THEN 8
WHEN 'Joined' THEN 9 WHEN 'Candidate' THEN 10 END,
PRIORITYID=@reasonid,
REMARKS=@reasonremarks,
SELECTIONREMARKS=@selectionremarks
WHERE ACTIONDETAILID=@actiondetailid";
public const string GET_CUSTOMER_AND_LEAD_PICKLIST = @"
SELECT xx.partybranchid AS Id,
xx.partybranchcode AS Code,
xx.partybranchname AS Name,
xx.geocode AS ClientGeoCode,
xx.selectiontype AS SelectionType
FROM (
SELECT a.PARTYBRANCHID, a.PARTYBRANCHCODE, a.PARTYBRANCHNAME,
c.GEOCODE, zip.ZIPCODE, f.CITYNAME, 'PartyBranch' AS SelectionType
FROM MPARTYBRANCH a, MPARTY b, MADDRESS c, MUSER e
CROSS APPLY (SELECT TOP(1) * FROM MZIP zip WHERE e.ZIPID=zip.ZIPID) zip,
MCITY f
WHERE a.PARTYID=b.PARTYID
AND a.DEFAULTADDRESSID=c.ADDRESSID
AND c.CITYID=CASE WHEN e.CITYID=-1 THEN c.CITYID ELSE e.CITYID END
AND c.ZIPCODE=CASE WHEN e.ZIPID=-1 THEN c.ZIPCODE ELSE zip.ZIPCODE END
AND e.USERID=@userid
AND c.CITYID=f.CITYID
AND a.PARTYBRANCHID<>-1
UNION ALL
SELECT a.LEADID, a.LEADCODE, a.LEADNAME,
c.GEOCODE, zip.ZIPCODE, f.CITYNAME, 'Lead' AS SelectionType
FROM TLEAD a, MCONTACT b
OUTER APPLY (SELECT TOP(1) c.* FROM MADDRESS c
WHERE c.OBJECTID=b.CONTACTID AND c.OBJECTTYPEID=-2147481759) c,
MUSER e
CROSS APPLY (SELECT TOP(1) * FROM MZIP zip WHERE e.ZIPID=zip.ZIPID) zip,
MCITY f
WHERE a.CONTACTID=b.CONTACTID
AND ISNULL(c.CITYID,-1)=CASE WHEN e.CITYID=-1 THEN ISNULL(c.CITYID,-1) ELSE e.CITYID END
AND ISNULL(c.ZIPCODE,'')=CASE WHEN e.ZIPID=-1 THEN ISNULL(c.ZIPCODE,'') ELSE zip.ZIPCODE END
AND e.USERID=@userid
AND c.CITYID=f.CITYID
AND a.LEADID<>-1
) xx
WHERE 1=1";
public const string GET_ACTION_SUMMARY = @"
SELECT a.ACTIONID AS ActionId,
a.TRANSACTIONDATE AS TransactionDate,
c.PARTYBRANCHID AS PartyBranchId,
c.PARTYBRANCHCODE AS PartyBranchCode,
c.PARTYBRANCHNAME AS PartyBranchName,
e.LEADID AS LeadId,
e.LEADCODE AS LeadCode,
e.LEADNAME AS LeadName,
f.EMPLOYEEID AS EmployeeId,
f.EMPLOYEECODE AS EmployeeCode,
f.EMPLOYEENAME AS EmployeeName,
CONVERT(VARCHAR(5), b.PERIODFROM, 108) AS PeriodFrom,
CONVERT(VARCHAR(5), b.PERIODTO, 108) AS PeriodTo,
a.REMARKS AS HeadRemarks,
b.REMARKS AS TailRemarks,
g.ACTIVITYID AS ActivityId,
g.ACTIVITYCODE AS ActivityCode,
g.ACTIVITYNAME AS ActivityName
FROM TACTION a, TACTIONDETAIL b, MPARTYBRANCH c, MPARTY d, TLEAD e, MEMPLOYEE f, MACTIVITY g
WHERE a.ACTIONID=b.ACTIONID
AND a.PARTYBRANCHID=c.PARTYBRANCHID
AND c.PARTYID=d.PARTYID
AND b.LEADID=e.LEADID
AND a.PERSONID=f.EMPLOYEEID
AND b.ACTIVITYID=g.ACTIVITYID";
public const string CHECK_OVERALL_LOCK_DATE = @"
SELECT ISNULL(OVERLOCKDATE, '1900-01-01') AS OverLockDate
FROM MORGANIZATIONUNIT
WHERE OUID = @ouid";
public const string CHECK_BACKDATE_SETTINGS = @"
SELECT BACKDATEENTRYDAY AS BackDateEntryDay,
BACKDATEUPDATEDAY AS BackDateUpdateDay,
BACKDATEDELETEDAY AS BackDateDeleteDay,
BACKDATECHECKTYPE AS BackDateCheckType,
FORWARDENTRYDAY AS ForwardEntryDay
FROM MBIZTRANSACTIONTYPE
WHERE BIZTRANSACTIONTYPEID = @biztransactiontypeid";
public const string CHECK_USER_RIGHTS = @"
SELECT SUBSTRING(rvm.ALLOW, 2, 1) AS CanInsert,
SUBSTRING(rvm.ALLOW, 3, 1) AS CanUpdate,
SUBSTRING(rvm.ALLOW, 4, 1) AS CanDelete
FROM MROLEVSMENU rvm
WHERE rvm.ROLEID = @roleid
AND rvm.MENUID = @menuid";
public const string SAVE_ACTION = @"INSERT INTO TACTION
(
ACTIONID,
OUID,
PERIODID,
BIZTRANSACTIONTYPEID,
TRANSACTIONNUMBER,
TRANSACTIONDATE,
REFERENCENUMBER,
REFERENCEDATE,
ENTRYMODE,
DEVICEID,
PARTYBRANCHID,
PERSONID,
PERIODFROM,
PERIODTO,
REMARKS,
TAGS,
OTHERCHARGESID,
STATUS,
VERSION,
CREATEDBYID,
CREATEDON,
MODIFIEDBYID,
MODIFIEDON,
ACTIONBYTYPE,
CREATEDAT,
MODIFIEDAT
)
VALUES
(
@TActionId,
@OrganizationUnitId,
@PeriodId,
@BIZTransactionTypeId,
@TActionTransactionNumber,
@TActionTransactionDate,
@TActionReferenceNumber,
@TActionReferenceDate,
@TActionEntryMode,
@DeviceId,
@PartyBranchId,
@PersonId,
@TActionPeriodFrom,
@TActionPeriodTo,
@TActionRemarks,
@TActionTags,
@OtherChargeId,
@TActionStatus,
@TActionVersion,
@TActionCreatedById,
@TActionCreatedOn,
@TActionModifiedById,
@TActionModifiedOn,
@TActionByType,
@TActionCreatedAt,
@TActionModifiedAt
);";
public const string UPDATE_ACTION = @"UPDATE TACTION SET
ACTIONID =@TActionId,
OUID = @OrganizationUnitId,
PERIODID =@PeriodId,
BIZTRANSACTIONTYPEID =@BIZTransactionTypeId,
TRANSACTIONNUMBER =@TActionTransactionNumber,
TRANSACTIONDATE=@TActionTransactionDate,
REFERENCENUMBER=@TActionReferenceNumber,
REFERENCEDATE=@TActionReferenceDate,
ENTRYMODE=@TActionEntryMode,
DEVICEID=@DeviceId,
PARTYBRANCHID=@PartyBranchId,
PERSONID=@PersonId,
PERIODFROM=@TActionPeriodFrom,
PERIODTO=@TActionPeriodTo,
REMARKS=@TActionRemarks,
TAGS=@TActionTags,
OTHERCHARGESID=@OtherChargeId,
STATUS=@TActionStatus,
VERSION=@TActionVersion,
CREATEDBYID=@TActionCreatedById,
CREATEDON=@TActionCreatedOn,
MODIFIEDBYID=@TActionModifiedById,
MODIFIEDON=@TActionModifiedOn,
ACTIONBYTYPE=@TActionByType,
CREATEDAT=@TActionCreatedAt,
MODIFIEDAT=@TActionModifiedAt
WHERE ACTIONID =@TActionId;
";
public const string DELETE_ACTION = @"DELETE FROM TACTION
WHERE ACTIONID=@tactionid;";
public const string SAVE_ACTION_DETAIL = @"
INSERT INTO TACTIONDETAIL (
ACTIONDETAILID, ACTIONID, SLNO, ACTIONTYPE,
TASKID, CALLID, BREAKDOWNID, LEADID,
PARTYBRANCHID, ACTIVITYID, ASSETID, PARAMETERSETID,
ACTIONSTATUS, REASONID, REMARKS, PERIODFROM, PERIODTO,
ISCOMPLETED, COMPLETEDON, DURATION, ALERTS, ALLOCATIONID,
ATTENDEDBYTYPE, ATTENDEDBYEMPLOYEEID, ATTENDEDBYCONTACTID, ATTENDEDBYPARTYID, ATTENDEDBYUSERID,
INTERVAL, NETDURATION, ISFREE, FCRATE, CURRENCYID, CONVERSIONRATE,
RATE, RATEPERUOMID, FCAMOUNT, AMOUNT, AMOUNTPLUS, AMOUNTMINUS,
GROSSAMOUNT, OTHERAMOUNTPLUS, OTHERAMOUNTMINUS, NETAMOUNT,
ISMACHINERUNNING, ISINVOICED, CHARGESID, FOLLOWUPTASKID, PENDINGREASONID,
completedPercentage, ACTIONDETAILSTATUS, PRIORITYID,
EXPECTEDDATE, EXPECTEDFCREVENUE, ORDERCONFIDENCE, REVENUECONFIDENCE,
SUCCESSSTATUS, LEADCYCLEDETAILID,
QUANTITY, NOOFDATA, OBJECTTYPEID, OBJECTID, WORKTYPEDETAILID,
SELECTIONREMARKS, SELECTIONSTATUS,
EXPENSEHEADID, PAIDTOACCOUNTID,
CLAIMAMOUNT, APPROVEDAMOUNT, RECOVERYPERCENTAGE, RECOVERYVALUE, RECOVERYTYPE
) VALUES (
@TActionDetailId, @ActionId, @TActionDetailSlno, @TActionDetailActionType,
@TaskId, @CallId, @TActionDetailBreakdown, @LeadId,
@PartyBranchId, @ActivityId, @AssetId, @ParameterSetId,
@TActionDetailActionStatus, @ReasonId, @TActionDetailRemarks,
@TActionDetailPeriodFrom, @TActionDetailPeriodTo,
@TActionDetailIsCompleted, @TActionDetailCompletedOn,
@TActionDetailDuration, @TActionDetailAlerts, @AllocationId,
@TActionDetailAttendedByType, @AttendedByEmployeeId, @AttendedByContactId,
@AttendedByPartyId, @AttendedByUserId,
@TActionDetailInterval, @TActionDetailNetDuration, @TActionDetailIsFree,
@TActionDetailFcRate, @CurrencyId, @TActionDetailConversionRate,
@TActionDetailRate, @RatePerUOMId, @TActionDetailFcAmount, @TActionDetailAmount,
@TActionDetailAmountPlus, @TActionDetailAmountMinus, @TActionDetailGrossAmount,
@TActionDetailOtherAmountPlus, @TActionDetailOtherAmountMinus, @TActionDetailNetAmount,
@TActionDetailIsMachineRunning, @TActionDetailIsInvoiced, @ChargeId,
@FollowUpTaskId, @PendingReasonId,
@TActionDetailCompletedPercentage, @TActionDetailActionDetailStatus, @PriorityId,
@TActionDetailExpectedDate, @TActionDetailExpectedFCRevenue,
@TActionDetailOrderConfidence, @TActionDetailRevenueConfidence,
@TActionDetailSuccessStatus, @LeadCycleDetailId,
@TActionDetailQuantity, @TActionDetailNoofData, @ObjectTypeId, @ObjectId, @WorkTypeDetailId,
@TActionDetailSelectionRemarks, @TActionDetailSelectionStatus,
@ExpenseHeadId, @PaidToAccountId,
@TActionDetailClaimAmount, @TActionDetailApprovedAmount,
@TActionDetailRecoveryPercentage, @TActionDetailRecoveryValue, @TActionDetailRecoveryType
);";
public const string DELETE_ACTION_DETAILS = @"
DELETE FROM TACTIONITEMS WHERE ACTIONDETAILID IN (SELECT ACTIONDETAILID FROM TACTIONDETAIL WHERE ACTIONID = @actionid);
DELETE FROM TACTIONDETAIL WHERE ACTIONID = @actionid;";
public const string UPDATE_TASK_FOR_ACTION = @"
UPDATE TTASK SET
TASKSTATUS = CASE @actionstatus WHEN 1 THEN 3 ELSE 0 END,
ACTUALSTARTON = @periodfrom,
ACTUALENDON = @periodto,
ACTUALHOURS = ACTUALHOURS + @actualhours,
ACTUALNUMBEROFDATA= ACTUALNUMBEROFDATA + @numberofdata,
MODIFIEDON = GETDATE()
WHERE TASKID = @taskid";
public const string REVERSE_TASK_FOR_ACTION = @"
UPDATE TTASK SET
ACTUALHOURS = CASE WHEN ACTUALHOURS >= @actualhours THEN ACTUALHOURS - @actualhours ELSE 0 END,
MODIFIEDON = GETDATE()
WHERE TASKID = @taskid";
public const string UPDATE_PARENT_TASK_FOR_ACTION_ADD = @"
WITH cte AS (
SELECT a.PARENTTASKID, a.TASKID
FROM TTASK a
WHERE a.TASKID = @taskid
UNION ALL
SELECT d.PARENTTASKID, p.TASKID
FROM cte p
JOIN TTASK d ON p.PARENTTASKID = d.TASKID
WHERE p.PARENTTASKID <> -1
)
UPDATE TTASK SET
ACTUALHOURS = ACTUALHOURS + @actualhours,
MODIFIEDON = GETDATE()
FROM cte
WHERE cte.PARENTTASKID = TTASK.TASKID
AND cte.TASKID = @taskid
AND cte.PARENTTASKID <> -1";
public const string UPDATE_PARENT_TASK_FOR_ACTION_REVERSE = @"
WITH cte AS (
SELECT a.PARENTTASKID, a.TASKID
FROM TTASK a
WHERE a.TASKID = @taskid
UNION ALL
SELECT d.PARENTTASKID, p.TASKID
FROM cte p
JOIN TTASK d ON p.PARENTTASKID = d.TASKID
WHERE p.PARENTTASKID <> -1
)
UPDATE TTASK SET
ACTUALHOURS = CASE WHEN ACTUALHOURS >= @actualhours THEN ACTUALHOURS - @actualhours ELSE 0 END,
MODIFIEDON = GETDATE()
FROM cte
WHERE cte.PARENTTASKID = TTASK.TASKID
AND cte.TASKID = @taskid
AND cte.PARENTTASKID <> -1";
public const string UPDATE_CALL_STATUS = @"
UPDATE TCALL SET
CALLSTATUS = @callstatus,
ISCLOSED = @isclosed,
COMPLETEDON = CASE WHEN @callstatus = 2 THEN @completedon ELSE COMPLETEDON END,
COMPLETEDONTIME = CASE WHEN @callstatus = 2 THEN @completedontime ELSE COMPLETEDONTIME END,
COMPLETEDBYID = CASE WHEN @callstatus = 2 THEN @userid ELSE COMPLETEDBYID END,
CLOSEDON = CASE WHEN @callstatus = 5 THEN @completedon ELSE CLOSEDON END,
CLOSEDBYID = CASE WHEN @callstatus = 5 THEN @userid ELSE CLOSEDBYID END
WHERE CALLID = @callid";
public const string UPDATE_LEAD = @"
UPDATE TLEAD SET
PRIORITYID = @priorityid,
EXPECTEDDATE = @expecteddate,
EXPECTEDFCREVENUE = @expectedfcrevenue,
ORDERCONFIDENCE = @orderconfidence,
REVENUECONFIDENCE = @revenueconfidence,
SUCESSSTATUS = @successstatus,
LEADSTATUS = @leadstatus,
ORDERWONLOSTDATE = CASE WHEN @successstatus IN (2,3) THEN @wonlostdate ELSE ORDERWONLOSTDATE END,
LOSTREMARKS = @lostremarks,
LEADCYCLEDETAILID = @leadcycledetailid
WHERE LEADID = @leadid";
// ── GB4: Geo-fencing / location tracking ──────────────────────────
public const string GET_BIZTRANSACTIONTYPE_ISLOCATIONTRACK = @"
SELECT ISLOCATIONTRACK FROM MBIZTRANSACTIONTYPE WHERE BIZTRANSACTIONTYPEID = @biztransactiontypeid";
public const string GET_GEOFENCING_SETTING = @"
SELECT GEOFENCING FROM MDBLEVELSETTING";
public const string POST_TO_LOCATION_TRACK = @"
EXEC POST_TO_LOCATION_TRACK @mastergeocode, @actualgeocode, @biztransactiontypeid, @userid";
// ── GB4: Task completed-percentage recalculation ──────────────────
public const string UPDATE_COMPLETED_PERCENTAGE = @"
UPDATE TTASK
SET COMPLETEDPERCENTAGE = CASE WHEN ISNULL(sub.Percentage,0) <= 100 THEN CAST(ISNULL(sub.Percentage,0) AS NUMERIC(5,2)) ELSE 100 END
FROM (
SELECT SUM((((b.ActivityTotalDuration * b.ActivityCompletedPercentage) / 100) / C.PLANNEDHOURS) * 100) AS Percentage, c.TASKID
FROM TTASK a,
(
SELECT OUID, PERIODID, TASKID, SUM(duration) ActivityTotalDuration, MAX(b.completedPercentage) ActivityCompletedPercentage
FROM TACTION a, TACTIONDETAIL b
WHERE a.ACTIONID = b.ACTIONID AND a.PERSONID = @personid
GROUP BY OUID, PERIODID, TASKID
) b,
TTASK c,
(SELECT PARENTTASKID, TASKID FROM TTASK WHERE TASKID = @taskid AND DETAILTYPE = @detailtype) D
WHERE a.TASKID = b.TASKID AND a.OUID = b.OUID AND a.PERIODID = b.PERIODID
AND a.PARENTTASKID = c.TASKID AND a.DETAILTYPE = @detailtype
AND a.PARENTTASKID = D.PARENTTASKID
GROUP BY C.TASKID
) sub
WHERE TTASK.TASKID = sub.TASKID";
public const string UPDATE_COMPLETED_PERCENTAGE_SECONDTIME = @"
UPDATE TTASK
SET COMPLETEDPERCENTAGE = CASE WHEN sub.Percentage <= 100 THEN CAST(ISNULL(sub.Percentage,0) AS NUMERIC(5,2)) ELSE 100 END
FROM (
SELECT a.PARENTTASKID,
CAST((SUM(A.PLANNEDHOURS * A.COMPLETEDPERCENTAGE / 100) / MAX(C.PLANNEDHOURS)) * 100 AS NUMERIC(20,2)) AS Percentage
FROM TTASK a, TTASK C
WHERE a.PARENTTASKID = c.TASKID AND a.DETAILTYPE = @detailtype
GROUP BY a.PARENTTASKID
) sub
WHERE sub.PARENTTASKID = TTASK.TASKID";
public const string UPDATE_COMPLETED_PERCENTAGE_RESOURCEPUNCH = @"
UPDATE TTASK
SET COMPLETEDPERCENTAGE = CAST((CAST(actualnumberofdata AS NUMERIC(18,4)) / CAST((CASE WHEN plannumberofdata = 0 THEN 1 ELSE plannumberofdata END) AS NUMERIC(18,4))) * 100 AS NUMERIC(18,4))
WHERE TASKID = @parenttaskid";
public const string GET_PARENT_TASK_ID = @"
SELECT PARENTTASKID FROM TTASK WHERE TASKID = @taskid";
}
}