using System; using System.Collections.Generic; using System.Diagnostics.Contracts; using System.Linq; using System.Text; using System.Threading.Tasks; using iText.Layout.Borders; using iText.StyledXmlParser.Jsoup.Select; using StackExchange.Redis; namespace FrameworkDAL.Query.Contact { public static class ContactQB { public const string GET_CONTACT = @"SELECT -- Contact Details MC.CONTACTID AS ContactId, MC.OBJECTTYPEID AS EntityId, ME.ENTITYCODE AS EntityCode, ME.ENTITYNAME AS EntityName, MC.OBJECTID AS ContactObjectId, MC.CONTACTNATURE AS ContactContactNature, MC.NAME AS ContactName, MC.FIRSTNAME AS ContactFirstName, MC.MIDDLENAME AS ContactMiddleName, MC.LASTNAME AS ContactLastName, MC.SALUTATION AS ContactSalutation, MC.SEX AS ContactSex, MC.DOB AS ContactDob, MC.MAILID AS ContactMailId, MC.SORTORDER AS ContactSortOrder, MC.VERSION AS ContactVersion, MC.STATUS AS ContactStatus, MC.SOURCETYPE AS ContactSourceType, MC.CREATEDBYID AS ContactCreatedById, CU.USERNAME AS ContactCreatedByName, -- from MUSER MC.CREATEDON AS ContactCreatedOn, MC.MODIFIEDBYID AS ContactModifiedById, MU.USERNAME AS ContactModifiedByName, -- from MUSER MC.MODIFIEDON AS ContactModifiedOn, MC.PHONENO AS ContactPhoneNo, MC.MOBILENO AS ContactMobileNo, MC.RELATIONSHIP AS ContactRelationShip, MC.DESIGNATION AS ContactDesignation, MC.COMPANY AS ContactCompany, MC.DEFAULTIMAGEID AS ContactDefaultImageId, MC.LINKTYPE AS ContactLinkType, MC.REPORTINGTOID AS ReportingToId, RT.NAME AS ReportingToName, -- from MCONTACT (self-join) MC.USERLINKID AS UserLinkId, UL.USERNAME AS LinkedUserName -- from MUSER FROM MCONTACT MC LEFT JOIN MENTITY ME ON ME.ENTITYID = MC.OBJECTTYPEID LEFT JOIN MUSER CU ON CU.USERID = MC.CREATEDBYID LEFT JOIN MUSER MU ON MU.USERID = MC.MODIFIEDBYID LEFT JOIN MCONTACT RT ON RT.CONTACTID = MC.REPORTINGTOID LEFT JOIN MUSER UL ON UL.USERID = MC.USERLINKID WHERE MC.CONTACTID = @ContactId;"; public const string SAVE_CONTACT = @"INSERT INTO MCONTACT ( CONTACTID, OBJECTTYPEID, OBJECTID, CONTACTNATURE, NAME, FIRSTNAME, MIDDLENAME, LASTNAME, SALUTATION, SEX, DOB, MAILID, SORTORDER, VERSION, STATUS, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, PHONENO, MOBILENO, RELATIONSHIP, DESIGNATION, COMPANY, DEFAULTIMAGEID, LINKTYPE, REPORTINGTOID, USERLINKID, THUMBNAIL ) VALUES ( @ContactId, @EntityId, @ContactObjectId, @ContactContactNature, @ContactName, @ContactFirstName, @ContactMiddleName, @ContactLastName, @ContactSalutation, @ContactSex, @ContactDob, @ContactMailId, @ContactSortOrder, @ContactVersion, @ContactStatus, @ContactSourceType, @ContactCreatedById, @ContactCreatedOn, @ContactModifiedById, @ContactModifiedOn, @ContactPhoneNo, @ContactMobileNo, @ContactRelationShip, @ContactDesignation, @ContactCompany, @ContactDefaultImageId, @ContactLinkType, @ReportingToId, @UserLinkId, @ContactThumbNail );"; public const string UPDATE_CONTACT = @"UPDATE MCONTACT SET OBJECTTYPEID = @EntityId, OBJECTID = @ContactObjectId, CONTACTNATURE = @ContactContactNature, NAME = @ContactName, FIRSTNAME = @ContactFirstName, MIDDLENAME = @ContactMiddleName, LASTNAME = @ContactLastName, SALUTATION = @ContactSalutation, SEX = @ContactSex, DOB = @ContactDob, MAILID = @ContactMailId, SORTORDER = @ContactSortOrder, VERSION = @ContactVersion, STATUS = @ContactStatus, SOURCETYPE = @ContactSourceType, MODIFIEDBYID = @ContactModifiedById, MODIFIEDON = @ContactModifiedOn, PHONENO = @ContactPhoneNo, MOBILENO = @ContactMobileNo, RELATIONSHIP = @ContactRelationShip, DESIGNATION = @ContactDesignation, COMPANY = @ContactCompany, DEFAULTIMAGEID = @ContactDefaultImageId, LINKTYPE = @ContactLinkType, REPORTINGTOID = @ReportingToId, USERLINKID = @UserLinkId, THUMBNAIL = @ContactThumbNail WHERE CONTACTID = @ContactId;"; public const string DELETE_CONTACT = @"DELETE FROM MCONTACT WHERE CONTACTID = @ContactId;"; // Base contact picklist (counter=1) public const string GET_SELECTLIST_CONTACT = @" WITH ContactList AS ( SELECT c.CONTACTID AS Id, c.Name AS Name, c.FirstName AS FirstName, c.MiddleName AS MiddleName, c.LastName AS LastName, c.MailId AS MailId, c.PhoneNo AS PhoneNo, c.MobileNo AS MobileNo, c.Company AS Company, c.RelationShip AS RelationShip, ROW_NUMBER() OVER (ORDER BY c.CONTACTID) AS RowNum FROM MCONTACT c ) SELECT * FROM ContactList WHERE (@firstNumber = -1 AND @maxResult = -1) OR (RowNum BETWEEN @firstNumber AND @maxResult); "; // Contact picklist with allocation (counter != 1) public const string GET_CONTACT_SQL_ALLOCATION_EFL = @" SELECT c.CONTACTID AS Id, c.Name AS Name, c.FirstName AS FirstName, c.MiddleName AS MiddleName, c.LastName AS LastName, c.MailId AS MailId, c.PhoneNo AS PhoneNo, c.MobileNo AS MobileNo, c.Company AS Company, c.RelationShip AS RelationShip, ISNULL(ma.ALLOCATIONID, -1) AS AllocationId, ISNULL(ma.ALLOCATIONNAME, '') AS AllocationName FROM MCONTACT c LEFT JOIN MALLOCATION ma ON ma.ALLOCATIONNAME = c.Name AND ma.ALLOCATIONTYPEID = -1500000000 WHERE 1=1;"; // Contact city info public const string GET_CONTACT_CITY = @" SELECT city.CITYID AS CityId, city.CITYCODE AS CityCode, city.CITYNAME AS CityName FROM MCONTACT mc INNER JOIN MADDRESS addr ON mc.CONTACTID = addr.ObjectId INNER JOIN MCITY city ON addr.CityId = city.CityId WHERE addr.ObjectTypeId = -2147481759 AND mc.CONTACTID = @contactId; "; // SQL list for special client public const string GET_CONTACT_SQLLIST = @" WITH ContactList AS ( SELECT C.CONTACTID AS Id, C.NAME AS Name, C.FIRSTNAME AS FirstName, C.LASTNAME AS LastName, ROW_NUMBER() OVER (ORDER BY C.CONTACTID) AS RowNum FROM MCONTACT C ) SELECT * FROM ContactList WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult); "; // Count query public const string GET_CONTACT_COUNT = @" SELECT COUNT(*) FROM MCONTACT C; "; public const string GET_SELECTLIST_CONTACT_BY_OBJECT = @" SELECT 'NONE' AS PartyCode, 'NONE' AS PartyName, 'NONE' AS PartyBranchCode, 'NONE' AS PartyBranchName, 'NONE' AS DefaultImageId, 'NONE' AS RelationShip, CASE SEX WHEN 0 THEN 'MALE' WHEN 1 THEN 'FEMALE' ELSE 'OTHER' END AS ContactSexName, SEX AS ContactSex, THUMBNAIL AS ContactThumbNail, CONTACTID AS ContactId, OBJECTTYPEID AS EntityId, 'CONTACT' AS EntityCode, 'Contact' AS EntityName, OBJECTID AS ContactObjectId, CONTACTNATURE AS ContactContactNature, NAME AS ContactName, FIRSTNAME AS ContactFirstName, MIDDLENAME AS ContactMiddleName, LASTNAME AS ContactLastName, SALUTATION AS ContactSalutation, DOB AS ContactDob, MAILID AS ContactMailId, PHONENO AS ContactPhoneNo, MOBILENO AS ContactMobileNo, RELATIONSHIP AS ContactRelationShip, DESIGNATION AS ContactDesignation, COMPANY AS ContactCompany, DEFAULTIMAGEID AS ContactDefaultImageId, LINKTYPE AS ContactLinkType, REPORTINGTOID AS ReportingToId, 'NONE' AS ReportingToName, NULL AS AddressDTO, 'NONE' AS ContactImageViewUrl, CREATEDBYID AS ContactCreatedById, CREATEDON AS ContactCreatedOn, MODIFIEDBYID AS ContactModifiedById, MODIFIEDON AS ContactModifiedOn, SORTORDER AS ContactSortOrder, STATUS AS ContactStatus, VERSION AS ContactVersion, SOURCETYPE AS ContactSourceType, NULL AS ContactAddon, 1 AS ContactIsUser, USERLINKID AS UserLinkId, 0 AS NumberOfRecords, NULL AS ContactStatusName FROM MCONTACT WHERE OBJECTTYPEID = @ObjectTypeId AND OBJECTID = @ObjectId ORDER BY NAME OFFSET (@FirstNumber - 1) ROWS FETCH NEXT @MaxResult ROWS ONLY;"; // Contact list decorated with an image view URL (ContactDefaultImageId -> attachment link). // LEFT JOINs (unlike the GB4 INNER JOIN) so contacts without an entity/reporting-to row still return. public const string GET_CONTACT_FOR_IMAGE = @"; WITH ContactList AS ( SELECT ROW_NUMBER() OVER (ORDER BY a.NAME) AS RowNum, a.CONTACTID AS ContactId, a.OBJECTTYPEID AS EntityId, b.ENTITYCODE AS EntityCode, b.ENTITYNAME AS EntityName, a.OBJECTID AS ContactObjectId, a.CONTACTNATURE AS ContactContactNature, a.NAME AS ContactName, a.FIRSTNAME AS ContactFirstName, a.MIDDLENAME AS ContactMiddleName, a.LASTNAME AS ContactLastName, a.SALUTATION AS ContactSalutation, a.SEX AS ContactSex, a.DOB AS ContactDob, a.MAILID AS ContactMailId, a.SORTORDER AS ContactSortOrder, a.STATUS AS ContactStatus, a.PHONENO AS ContactPhoneNo, a.MOBILENO AS ContactMobileNo, a.RELATIONSHIP AS ContactRelationShip, a.DESIGNATION AS ContactDesignation, a.COMPANY AS ContactCompany, a.DEFAULTIMAGEID AS ContactDefaultImageId, a.LINKTYPE AS ContactLinkType, a.REPORTINGTOID AS ReportingToId, c.NAME AS ReportingToName, a.USERLINKID AS UserLinkId, a.THUMBNAIL AS ContactThumbNail FROM MCONTACT a LEFT JOIN MENTITY b ON b.ENTITYID = a.OBJECTTYPEID LEFT JOIN MCONTACT c ON c.CONTACTID = a.REPORTINGTOID WHERE 1 = 1 ) SELECT * FROM ContactList WHERE (@FirstNumber = -1 AND @MaxResult = -1) OR (RowNum BETWEEN @FirstNumber AND @MaxResult) ORDER BY ContactName;"; } }