public area GetCurrentDistrict(string longitude, string latitude)
{
string sql = @"
select new_districtId,Area,distric.new_cityid,distric.new_name distname,city.new_name
from HourlyDB.dbo.new_districtBase distric left outer join HourlyDB.dbo.new_CityBase city on
distric.new_cityid=city.new_CityId
where CHARINDEX('@long @lat',convert(nvarchar(max),area,2)) > 0
";
sql = @"
select Area, new_districtId,Area,distric.new_cityid,distric.new_name,city.new_name,convert(nvarchar(max),area)
from HourlyDB.dbo.new_districtBase distric left outer join HourlyDB.dbo.new_CityBase city on
distric.new_cityid=city.new_CityId";
// sql=sql.Replace("@long",longitude).Replace("@lat",latitude);
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
area areas = new area();
if (dt.Rows.Count > 0)
{
areas = new area()
{
district = new District()
{
name = dt.Rows[0]["distname"].ToString(),
districtId = dt.Rows[0]["new_districtId"].ToString(),
},
City = new Models.City()
{
Name = dt.Rows[0]["new_name"].ToString(),
CityId = dt.Rows[0]["new_cityid"].ToString(),
}
}
}
;
return(areas);
}
/// <summary>
/// modified by m.abdelrahman remove lang segment from query
/// </summary>
/// <param name="entityName"></param>
/// <param name="optionSetFieldName"></param>
/// <param name="lang"></param>
/// <returns></returns>
public IEnumerable <BaseOptionSet> GetOptionSetLookup(string entityName, string optionSetFieldName, UserLanguage lang = UserLanguage.English, string concattext = "")
{
//int langId = (lang == UserLanguage.English ? 1033 : 1025);
int langId, oppositeLangId;
switch (lang)
{
case UserLanguage.Arabic:
langId = 1025;
oppositeLangId = 1033;
break;
default:
langId = 1033;
oppositeLangId = 1025;
break;
}
string query = String.Format(@";With main as(
Select distinct [Value] as Text, AttributeValue as Value
From StringMap s Inner Join EntityLogicalView e
ON s.ObjectTypeCode = e.ObjectTypeCode
Where e.[Name]='{0}' and s.AttributeName = '{1}' and LangId = {2}
) ,
Opposite as (
select distinct opposite_s.[Value] as Text, opposite_s.AttributeValue as Value
From StringMap opposite_s Inner Join EntityLogicalView e
ON opposite_s.ObjectTypeCode = e.ObjectTypeCode
Where e.[Name]='{3}' and opposite_s.AttributeName = '{4}' and opposite_s.LangId = {5}
)
select distinct ISNULL( main.Text , opposite.Text) as Text, ISNULL(main.Value,opposite.Value) as Value
from main full outer join Opposite on main.Value = Opposite.Value",
entityName, optionSetFieldName, langId, entityName, optionSetFieldName, oppositeLangId);
var dt = CRMAccessDB.SelectQ(query).Tables[0];
return(dt.AsEnumerable().Select(row => new BaseOptionSet()
{
Key = (int)row["Value"], Value = row["Text"].ToString() + concattext
}).OrderBy(a => a.Key));
}
public List <Car> Get()
{
string sql = @"select car.new_carresourceId,car.new_name from new_carresourceBase car";
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <Car> List = new List <Car>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new Car()
{
Id = dt.Rows[i][0].ToString(),
Name = dt.Rows[i][1].ToString(),
});
}
return(List);
}
public List<OptionList> GetComplaintsStatus(int lang = 0)
{
string sql = SqlQuery.Replace("@entityname", "new_csindvsector").Replace("@optionname", "statuscode")
.Replace("@lang", lang == 0 ? "1025" : "1033");
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List<OptionList> list = new List<OptionList>();
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(new OptionList() { Id = dt.Rows[i]["AttributeValue"].ToString(), Name = dt.Rows[i]["Value"].ToString() });
}
return list.Where(a => a.Id != "1" && a.Id != "2").ToList();
}
public List<OptionList> GetPricingList(int lang = 0)
{
string sql = SqlQuery.Replace("@entityname", "new_indvprice").Replace("@optionname", "new_pricetype")
.Replace("@lang", lang == 0 ? "1025" : "1033");
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List<OptionList> list = new List<OptionList>();
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(new OptionList() { Id = dt.Rows[i]["AttributeValue"].ToString(), Name = dt.Rows[i]["Value"].ToString() });
}
list = list.Where(a => a.Id != "1" && a.Id != "60" && a.Name != "اخرى").ToList();
return list;
}
public IEnumerable <City> GetAllCity()
{
string sql = @"SELECT distinct city.new_CityId CityId,city.new_name Name from new_CityBase city,new_districtBase
where city.new_CityId=new_districtBase.new_cityid order by city.new_name ";
System.Data.DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <City> CityList = new List <City>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CityList.Add(new City()
{
CityId = dt.Rows[i][0].ToString(), Name = dt.Rows[i][1].ToString()
});
}
return(CityList.GroupBy(x => x.Name)
.Select(group => group.First()).Where(a => a.Name != "الاحساء"));
}
public IEnumerable <AvailableNumbers> GetAvailableNumbers()
{
string SQL = @"select employee.new_nationalityId,employee.new_nationalityIdName as new_nationalityName ,employee.new_professionId,employee.new_professionIdName as new_professionName,
count(distinct employee.new_EmployeeId) as availablecount from new_employee employee
inner join new_indvprice on new_nationality = employee.new_nationalityId
and new_profession = employee.new_professionId
where employee.statecode = 0
and employee.new_employeetype = 3
and employee.statuscode in(279640012,1,279640001,279640000)
and employee.new_indivcontract is null
and new_indvprice.statecode = 0 and new_indvprice.new_forweb = 1
group by employee.new_nationalityId,employee.new_nationalityIdName,employee.new_professionId,employee.new_professionIdName" ;
DataTable dt = CRMAccessDB.SelectQ(SQL).Tables[0];
List <AvailableNumbers> List = new List <AvailableNumbers>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!List.Any(a => a.Nationality == dt.Rows[i]["new_nationalityName"].ToString()))
{
AvailableNumbers n = new AvailableNumbers();
n.ProfessionCounts = new List <ProfessionCount>();
n.Nationality = dt.Rows[i]["new_nationalityName"].ToString();
n.NationalityId = dt.Rows[i]["new_nationalityId"].ToString();
n.ProfessionCounts.Add(new ProfessionCount()
{
Profession = dt.Rows[i]["new_professionName"].ToString(),
ProfessionId = dt.Rows[i]["new_professionId"].ToString(),
Count = dt.Rows[i]["availablecount"].ToString(),
});
List.Add(n);
}
else
{
List.FirstOrDefault(a => a.Nationality == dt.Rows[i]["new_nationalityName"].ToString()).ProfessionCounts.Add(new ProfessionCount()
{
Profession = dt.Rows[i]["new_professionName"].ToString(),
ProfessionId = dt.Rows[i]["new_professionId"].ToString(),
Count = dt.Rows[i]["availablecount"].ToString(),
});
}
}
return(List);
}
public List<OptionList> GetSectors(int lang = 0)
{
//new_company_busienss
string sql = SqlQuery.Replace("@entityname", "account").Replace("@optionname", "industrycode")
.Replace("@lang", lang == 0 ? "1025" : "1033");
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List<OptionList> list = new List<OptionList>();
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(new OptionList() { Id = dt.Rows[i]["AttributeValue"].ToString(), Name = dt.Rows[i]["Value"].ToString() });
}
return list;
}
public List <District> GetCityDistricts(string id)
{
string sql = @"SELECT new_districtId districtId,new_name name from new_districtBase
where new_cityid='@cityId' order by new_name ";
sql = sql.Replace("@cityId", id);
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <District> List = new List <District>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new District()
{
districtId = dt.Rows[i][0].ToString(),
name = dt.Rows[i][1].ToString(),
});
}
return(List.GroupBy(x => x.name).Select(group => group.First()).Where(a => a.name != "الأسكان").ToList());
}
public List <District> Get()
{
string sql = @"SELECT new_districtId districtId,new_name name,new_days days,new_shifts shifts,versionnumber from new_districtBase
where new_days IS NOT NULL AND LEN(new_days) > 0
AND new_shifts IS NOT NULL AND LEN(new_shifts) > 0 ";
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <District> List = new List <District>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new District()
{
districtId = dt.Rows[i][0].ToString(),
name = dt.Rows[i][1].ToString(),
days = dt.Rows[i][2].ToString(),
shifts = dt.Rows[i][3].ToString()
});
}
return(List);
}
public IEnumerable <HourlyPricing> Get(string NationalityId, string Shift)
{
string sql = @"SELECT new_hourlypricingId hourlypricingId,new_name name,new_visitcount visitcount,new_visitprice visitprice,
new_discount discount,new_hours [hours],new_noofmonths noofmonths,new_totalvisits totalvisit,
new_totalprice totalprice,new_monthvisits monthvisits,versionnumber,new_hourprice from new_hourlypricingBase
Where new_nationality='@nationalityId' and new_shift=@shift";
int shifttype = 1;
if (Shift.ToLower() == "morning")
{
shifttype = 0;
}
sql = sql.Replace("@nationalityId", NationalityId);
sql = sql.Replace("@shift", shifttype.ToString());
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <HourlyPricing> List = new List <HourlyPricing>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new HourlyPricing()
{
HourePrice = dt.Rows[i]["new_hourprice"].ToString(),
hourlypricingId = dt.Rows[i][0].ToString(),
Name = dt.Rows[i][1].ToString(),
VisitCount = dt.Rows[i][2].ToString(),
VisitPrice = dt.Rows[i][3].ToString(),
Discount = dt.Rows[i][4].ToString(),
Hours = dt.Rows[i][5].ToString(),
NoOfMonths = dt.Rows[i][6].ToString(),
TotalVisit = dt.Rows[i][7].ToString(),
TotalPrice = dt.Rows[i][8].ToString(),
MonthVisits = dt.Rows[i][9].ToString(),
VersionNumber = dt.Rows[i][10].ToString()
});
}
return(List);
}
public IEnumerable <Employee> GetAvailableEmployees(string nationalityId, string professionId)
{
string SQL = @"select employee.new_nationalityIdName as new_nationalityName ,employee.new_professionIdName as new_professionName ,
candidate.new_cancareold
,candidate.new_cancook
,candidate.new_candowithchildren
,candidate.new_canspeakarabic
,candidate.new_canspeakenglish
,employee.* from new_employee employee, new_candidate candidate
where
employee.new_CandidateIld = candidate.new_candidateId
and employee.new_nationalityId = '@nationalityId'
and employee.new_professionId = '@professionId'
and employee.statecode = 0
and employee.new_employeetype = 3
and employee.statuscode in(279640012,1,279640001,279640000)
and employee.new_indivcontract is null";
SQL = SQL.Replace("@nationalityId", nationalityId);
SQL = SQL.Replace("@professionId", professionId);
DataTable dt = CRMAccessDB.SelectQ(SQL).Tables[0];
List <Employee> List = new List <Employee>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new Employee()
{
Name = dt.Rows[i]["new_name"].ToString(),
IdNumber = dt.Rows[i]["new_empidnumber"].ToString(),
EmployeeId = dt.Rows[i]["new_idnumber"].ToString(),
JobNumber = dt.Rows[i]["new_EmployeeId"].ToString(),
JobTitle = dt.Rows[i]["new_professionName"].ToString(),
Nationality = dt.Rows[i]["new_nationalityName"].ToString(),
Region = dt.Rows[i]["new_religion"].ToString(),
Skills = (dt.Rows[i]["new_cancook"].ToString() == "True" ? "تجيد الطبخ والتنظيف " : "") + (dt.Rows[i]["new_candowithchildren"].ToString() == "True" ? "/n تجيد معاملة الأطفال " : "") + (dt.Rows[i]["new_cancareold"].ToString() == "True" ? " /n تجيد معاملة كبار السن" : "") + (dt.Rows[i]["new_canspeakarabic"].ToString() == "True" ? " /n تجيد اللغة العربية" : "") + (dt.Rows[i]["new_canspeakenglish"].ToString() == "True" ? " /n تجيد اللغة الإنجليزية" : ""),
Image = dt.Rows[i]["new_fullpicture"].ToString() == "" ? "Imagess/manmajhol.png" : "Images/" + dt.Rows[i]["new_fullpicture"].ToString(),
});
}
return(List);
}
public List <DomesticInvoice> GetDomesticInvoices(string userId, UserLanguage Lang)
{
string optionSetGetValFn, otherLangOptionSetGetValFn;
switch (Lang)
{
case UserLanguage.Arabic:
optionSetGetValFn = "dbo.getOptionSetDisplay";
otherLangOptionSetGetValFn = "dbo.getOptionSetDisplayen";
break;
default:
optionSetGetValFn = "dbo.getOptionSetDisplayen";
otherLangOptionSetGetValFn = "dbo.getOptionSetDisplay";
break;
}
var query = String.Format(@" Select new_indvpaymentid , new_sabnumber , new_indvcontractid , new_paymentduedate, new_fromdate , new_todate, new_custamount ,
case when new_totalamountwithvat is null then (isnull(new_vatrate,0)*new_invoiceamount + new_invoiceamount) else new_totalamountwithvat end as new_totalamountwithvat, new_paymenttype,new_ispaid, new_customer, new_indvcontractidname , new_customername,
Isnull({2}('new_paymenttype','{1}',new_paymenttype),{3}('new_paymenttype','{1}',new_paymenttype) ) as new_paymenttypename,
contact.mobilephone
From new_indvpayment left outer join contact on contact.contactid = new_indvpayment.new_customer
Where new_customer = '{0}'
", userId, CrmEntityName, optionSetGetValFn, otherLangOptionSetGetValFn);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
if (dt.Rows.Count == 0)
{
return(null);
}
List <DomesticInvoice> invoices = new List <DomesticInvoice>();
foreach (DataRow item in dt.Rows)
{
var invoice = new DomesticInvoice(item);
invoices.Add(invoice);
}
return(invoices);
}
public HttpResponseMessage GetDistrictDays(string id)
{
List <string> result;
if (ConfigurationManager.AppSettings["OpenDays"] == "true")
{
result = Enum.GetNames(typeof(DayOfWeek)).ToList();
}
string sql = @"SELECT new_days days from new_districtBase
where new_districtId='@new_districtId' AND new_days IS NOT NULL AND LEN(new_days) > 0
AND new_shifts IS NOT NULL AND LEN(new_shifts) > 0 ";
sql = sql.Replace("@new_districtId", id);
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <string> List = new List <string>();
List = dt.Rows[0][0].ToString().Split(',').ToList();
result = List;
return(OkResponse <List <string> >(result));
}
public IEnumerable <string> GetVisits()
{
string sql = "";
sql = @"SELECT distinct hourPrice.new_visitcount noofmonths
from new_hourlypricingBase hourPrice,new_country country
Where hourPrice.new_nationality=country.new_CountryId
";
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <string> List = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(
dt.Rows[i][0].ToString()
);
}
return(List);
}
public IEnumerable <City> Get(int lang = 0)
{
string sql = @"SELECT distinct city.new_CityId CityId,city.new_name Name,city.versionnumber,new_englsihName from new_CityBase city,new_districtBase
where city.new_CityId=new_districtBase.new_cityid and (new_districtBase.new_days IS NOT NULL AND LEN(new_districtBase.new_days) > 0
AND new_districtBase.new_shifts IS NOT NULL AND LEN(new_districtBase.new_shifts) > 0 ) order by city.new_name ";
System.Data.DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <City> CityList = new List <City>();
string name = "Name";
if (lang == 1)
{
name = "new_englsihName";
}
for (int i = 0; i < dt.Rows.Count; i++)
{
CityList.Add(new City()
{
CityId = dt.Rows[i][0].ToString(), Name = dt.Rows[i][name].ToString()
});
}
return(CityList);
}
public IEnumerable <Lead> GetLeadsByMobile(string Mobile, UserLanguage lang)
{
string functionToGetProblemsName = lang == UserLanguage.Arabic ? "getOptionSetDisplay" : "getOptionSetDisplayen";
string query = String.Format(@"select
cl.LeadId,
cl.new_cityId,
cl.new_cityidName,
cl.new_districtidName,
cl.new_regionName,
cl.FirstName,
cl.LastName,
cl.companyname,
cl.new_sector,
[dbo].[{0}]('new_sector','Lead', cl.new_sector) as sectorName,
cl.industrycode,
cl.new_salespersonName,
cl.new_molfile,
cl.new_medicallead,
cl.mobilephone,
cl.emailaddress1,
cl.Description,
cl.StatusCode,
[dbo].[{0}]('StatusCode','Lead', cl.StatusCode) as statusName,
cl.new_leadservicetype,
[dbo].[{0}]('new_leadservicetype','Lead', cl.new_leadservicetype) as serviceTypeName,
cl.address1_line1,
cl.JobTitle,
cl.CreatedOn
from Lead cl
where cl.mobilephone = '{1}' and
cl.CreatedOn BETWEEN DATEADD(day,DATEDIFF(day,2,GETDATE()),0) and GETDATE()", functionToGetProblemsName, Mobile);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
return(dt.AsEnumerable().Select(dataRow => new Lead(dataRow)));
}
public decimal GetDiscount(string HPId, int HoursCount, int totalVisitsCount)
{
string SqlDiscount = @"
select Top(1) new_discount,new_name,new_hourcount,new_noofmonths,new_daysperweek from new_hourdiscountlist
where new_hourlypricing='@HourlyPricingId'
and new_hourcount=@hourscount and new_daysperweek <= @dayscount
order by new_daysperweek desc
";
SqlDiscount = SqlDiscount.Replace("@HourlyPricingId", HPId);
SqlDiscount = SqlDiscount.Replace("@hourscount", HoursCount.ToString());
SqlDiscount = SqlDiscount.Replace("@dayscount", totalVisitsCount.ToString());
DataTable dt = CRMAccessDB.SelectQ(SqlDiscount).Tables[0];
List <string> List = new List <string>();
if (dt.Rows.Count == 0)
{
return(0m);
}
return(decimal.Parse(dt.Rows[0]["new_discount"].ToString()));
}
public IEnumerable <Nationality> Get(bool HasHourlyPricing = false)
{
string sql;
if (!HasHourlyPricing)
{
sql = @"SELECT new_CountryId CountryId , new_name name,new_code code,new_isocode isocode,
new_NameEnglish NameEnglish,new_axcode axcode ,versionnumber from new_CountryBase order by new_name";
}
else
{
sql = @"SELECT distinct country.new_CountryId CountryId ,country.new_name name,country.new_code code,country.new_isocode isocode,
country.new_NameEnglish NameEnglish,country.new_axcode axcode ,country.versionnumber
from new_CountryBase country, new_hourlypricingBase hourPrice
where country.new_CountryId =hourPrice.new_nationality order by country.new_name ";
}
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <Nationality> List = new List <Nationality>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new Nationality()
{
CountryId = dt.Rows[i][0].ToString(),
Name = dt.Rows[i][1].ToString(),
Code = dt.Rows[i][2].ToString(),
IsOCode = dt.Rows[i][3].ToString(),
NameEnglish = dt.Rows[i][4].ToString(),
AXCode = dt.Rows[i][5].ToString(),
VersionNumber = dt.Rows[i][6].ToString()
});
}
return(List);
}
internal IEnumerable <District> GetDistrictsByCity(string id)
{
string sql = @"SELECT new_districtId districtId,new_name name,new_days days,new_shifts shifts,versionnumber from new_districtBase
where new_cityid='@cityId' AND new_days IS NOT NULL AND LEN(new_days) > 0
AND new_shifts IS NOT NULL AND LEN(new_shifts) > 0 order by new_name ";
sql = sql.Replace("@cityId", id);
List <District> newDistricts = CRMAccessDB.SelectQ(sql).Tables[0].AsEnumerable().Select(dataRow => new District(dataRow)).ToList();
newDistricts.ForEach(t =>
{
if (t.days.IndexOf(",Friday") > -1)
{
t.days = t.days.Replace(",Friday", "");
}
if (t.days.IndexOf("Friday") > -1)
{
t.days = t.days.Replace("Friday", "");
}
});
return(newDistricts.Select(t => t));
}
public IEnumerable <CustomerTicket> GetDalalCustomerTickets(string sectorId, string userId, UserLanguage lang, string statusCode = null)
{
string statusCondition = String.IsNullOrEmpty(statusCode) ? string.Empty : String.Format(" and ticket.statuscode={0} ", statusCode);
//string functionToGetProblemsName = lang == UserLanguage.Arabic ? "getOptionSetDisplay" : "getOptionSetDisplayen";
string optionSetGetValFn, otherLangOptionSetGetValFn;
switch (lang)
{
case UserLanguage.Arabic:
optionSetGetValFn = "dbo.getOptionSetDisplay";
otherLangOptionSetGetValFn = "dbo.getOptionSetDisplayen";
break;
default:
optionSetGetValFn = "dbo.getOptionSetDisplayen";
otherLangOptionSetGetValFn = "dbo.getOptionSetDisplay";
break;
}
string query = String.Format(@"select ticket.new_csindvsectorId,ticket.new_name,ticket.new_indvcontractid,ticket.new_problemdetails,ticket.statuscode,ticket.new_empbussid,ticket.new_closedno,
ticket.new_name as contractName,ticket.new_contactName,ticket.CreatedOn,ticket.new_contracttype,ticket.new_problemcase,
Isnull({0}('{1}','{2}', ticket.new_problemcase), {3}('{1}','{2}', ticket.new_problemcase)) as new_problemcaseName,
Isnull({0}('{4}','{2}', ticket.new_contracttype),{3}('{4}','{2}', ticket.new_contracttype)) as new_contracttypeName,
Isnull({0}('{5}','{2}', ticket.statuscode),{3}('{5}','{2}', ticket.statuscode)) as statuscodeName
from new_csindvsector ticket
left outer join contact
on ticket.new_contact = contact.ContactId
where ticket.new_contracttype= {6} and ticket.new_contact = '{7}' {8}", optionSetGetValFn, "new_problemcase", "new_csindvsector", otherLangOptionSetGetValFn, "new_contracttype", "statuscode", sectorId, userId, statusCondition);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
return(dt.AsEnumerable().Select(dataRow => new CustomerTicket(dataRow)));
}
public Promotion GetPromotionByCode(RequestHourlyPricing requestHourlyPricing, UserLanguage Lang)
{
string code = requestHourlyPricing.PromotionCode;
if (string.IsNullOrEmpty(code))
{
code = DefaultValues.ServiceContractPerHour_DefaultPromotionCode;
}
string query = String.Format(@"select new_promotionslistId,
new_name,new_code,new_description,new_freevisits,new_discount,
new_fixeddiscount,new_availalbe,new_fromdate,new_todate
from new_promotionslist where new_availalbe = 1 and new_code='{0}'", code);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
if (dt.Rows.Count == 0 && code != DefaultValues.ServiceContractPerHour_DefaultPromotionCode)
{
requestHourlyPricing.PromotionCode = DefaultValues.ServiceContractPerHour_DefaultPromotionCode;
return(GetPromotionByCode(requestHourlyPricing, Lang));
}
bool checkresult = true;
PropertyInfo[] properties = typeof(RequestHourlyPricing).GetProperties();
InvalidProperties = new List <string>();
foreach (PropertyInfo property in properties)
{
string propValue = property.GetValue(requestHourlyPricing) != null?property.GetValue(requestHourlyPricing).ToString() : "";
if (!string.IsNullOrEmpty(propValue))
{
bool isPromotionValid = checkpromotionvalidation(property.Name, propValue, dt.Rows[0]["new_promotionslistId"].ToString());
checkresult = isPromotionValid && checkresult;
}
}
if (checkresult == false)
{
//requestHourlyPricing.PromotionCode = DefaultValues.ServiceContractPerHour_DefaultPromotionCode;
string query1 = String.Format(@"select new_promotionslistId,
new_name,new_code,new_description,new_freevisits,new_discount,
new_fixeddiscount,new_availalbe,new_fromdate,new_todate
from new_promotionslist where new_code='{0}'", DefaultValues.ServiceContractPerHour_DefaultPromotionCode);
DataTable dt1 = CRMAccessDB.SelectQ(query1).Tables[0];
var p = new Promotion(dt1.AsEnumerable().FirstOrDefault());
//var p = GetPromotionByCode(requestHourlyPricing);
string errorMessage = "";
if (Lang == UserLanguage.Arabic)
{
errorMessage = "كود الخصم غير مناسب مع الباقة المختارة";
}
else
{
errorMessage = "Invalid Promotion With Selected Package. ";
}
//if (InvalidProperties.Any())
//{
// InvalidProperties.ForEach(s =>
// {
// errorMessage += " [ " + s + " ] ";
// });
//}
p.Name = errorMessage;
return(p);
}
var validPromotion = new Promotion(dt.AsEnumerable().FirstOrDefault());
validPromotion.Name = validPromotion.Description;
return(validPromotion);
//return dt.AsEnumerable().Select(dataRow => new Promotion(dataRow));
}
public bool checkpromotionvalidation(string fieldname, string value, string promoid)
{
string query = @" select * from new_promovalidation
inner join new_promotionslist on new_promovalidation.new_promotionvalidationid=new_promotionslist.new_promotionslistId
where new_promovalidation.new_promotionvalidationid='@id' and new_fieldname='@fieldname'";
query = query.Replace("@id", promoid);
query = query.Replace("@fieldname", fieldname);
DataTable dtvalidationvalues = CRMAccessDB.SelectQ(query).Tables[0];
if (dtvalidationvalues.Rows.Count == 0)
{
return(true);
}
bool checkresult = true;
for (int i = 0; i < dtvalidationvalues.Rows.Count; i++)
{
decimal resultdecimal = 0;
decimal valuedecimal = 0;
if (checkresult == false)
{
InvalidProperties.Add(fieldname);
return(checkresult);
}
if (decimal.TryParse(dtvalidationvalues.Rows[i]["new_fieldvalue"].ToString(), out resultdecimal) && decimal.TryParse(value, out valuedecimal))
{
switch (dtvalidationvalues.Rows[i]["new_operator"].ToString())
{
case "<":
checkresult = (valuedecimal < resultdecimal) && checkresult;
break;
case ">":
checkresult = (valuedecimal > resultdecimal) && checkresult;
break;
case "<=":
checkresult = (valuedecimal <= resultdecimal) && checkresult;
break;
case ">=":
checkresult = (valuedecimal >= resultdecimal) && checkresult;
break;
case "=":
checkresult = (valuedecimal == resultdecimal) && checkresult;
break;
case "==":
checkresult = (valuedecimal == resultdecimal) && checkresult;
break;
default:
break;
}
}
else
{
checkresult = (value == dtvalidationvalues.Rows[i]["new_fieldvalue"].ToString()) && checkresult;
}
}
return(checkresult);
}
public ServiceContractPerHour GetHourlyContractDetails(string contractId, UserLanguage Language)
{
string optionSetGetValFn, otherLangOptionSetGetValFn;
switch (Language)
{
case UserLanguage.Arabic:
optionSetGetValFn = "dbo.getOptionSetDisplay";
otherLangOptionSetGetValFn = "dbo.getOptionSetDisplayen";
break;
default:
optionSetGetValFn = "dbo.getOptionSetDisplayen";
otherLangOptionSetGetValFn = "dbo.getOptionSetDisplay";
break;
}
string query = String.Format(@"select contract.new_hindvcontractid,
contract.new_contractnumber,
hourlypricing.new_name as hourlypricingname,
Contract.new_finalprice,
Contract.new_contractmonth,
Isnull(appointment.userrate,0) as userrate,
appointment.nextappointment,
contract.new_city,
contract.new_cityName,
contract.new_district,
contract.new_districtName ,
hourlypricing.new_nationalityName,
hourlypricing.new_nationality,
contract.new_selecteddays,
contract.new_shift,
contract.new_vatrate as vatrate,
contract.new_hoursnumber,
contract.new_weeklyvisits,
contract.new_totalprice_def as totalprice,
contract.new_totalprice_def ,
ISNULL(contract.new_vatrate,0) * 100 as new_vatrate,
contract.new_vatamount,
contract.new_discount_def,
Round(contract.new_weeklyvisits * contract.new_hoursnumber * Contract.new_contractmonth * hourlypricing.new_hourprice,2,2) as totalPriceBeforeDiscount,
contract.new_employeenumber,
contract.new_latitude,
contract.new_longitude,
Convert(date, contract.new_contractstartdate) as new_contractstartdate,
Isnull({0}('new_contractmonth','new_HIndvContract',Contract.new_contractmonth),{1}('new_contractmonth','new_HIndvContract',Contract.new_contractmonth) ) as durationname,
Contract.statuscode,
Contract.createdon,
Isnull({2}('statuscode','new_HIndvContract',Contract.statuscode),{3}('statuscode','new_HIndvContract',Contract.statuscode) ) as statusname,
contract.new_HIndivClintnameName,
contact.mobilephone,
contract.new_HIndivClintname
--contract.new_discount_def,contract.new_totalprice_def,contract.new_totalvisits_def,contract.new_monthvisits_def,
--contract.new_contractmonth,,Contract.new_selectedshifts,Contract.new_visitprice_def,
--
--hourlypricing.new_hourlypricingId,
from new_HIndvContract contract inner join new_hourlypricing hourlypricing on contract.new_houlrypricing=hourlypricing.new_hourlypricingId
left outer join (
select new_hourlyappointmentBase.new_servicecontractperhour,
IIF( Count(new_hourlyappointmentBase.new_rate) > 0, ROUND( Sum(Isnull(new_hourlyappointmentBase.new_rate,0)) / Count(new_hourlyappointmentBase.new_rate), 0) , null) as userrate,
max(new_hourlyappointmentBase.new_shiftstart) as nextAppointment
from new_hourlyappointmentBase
where Convert (date, new_shiftstart) <= Convert(date,GetDate())
group by new_servicecontractperhour
) as appointment on new_servicecontractperhour= contract.new_HIndvContractId
left outer join contact on contact.contactid = contract.new_HIndivClintname
where contract.new_hindvcontractid = '{4}'
order by contract.new_ContractNumber desc", optionSetGetValFn, otherLangOptionSetGetValFn, optionSetGetValFn, otherLangOptionSetGetValFn, contractId);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
if (dt.Rows.Count == 0)
{
return(null);
}
var contract = new ServiceContractPerHour(dt.Rows[0], Language);
contract.HourlyPricingCost = new HourlyPricingCost(dt.Rows[0]);
return(contract);
}
internal IEnumerable <ServiceContractPerHour> GetUserHourlyContracts(string userId, string statusCode, UserLanguage Language)
{
//Get Customer Contracts
string statusCondition = String.IsNullOrEmpty(statusCode) ? string.Empty : String.Format(" And contract.statuscode = {0} ", statusCode);
string optionSetGetValFn, otherLangOptionSetGetValFn;
switch (Language)
{
case UserLanguage.Arabic:
optionSetGetValFn = "dbo.getOptionSetDisplay";
otherLangOptionSetGetValFn = "dbo.getOptionSetDisplayen";
break;
default:
optionSetGetValFn = "dbo.getOptionSetDisplayen";
otherLangOptionSetGetValFn = "dbo.getOptionSetDisplay";
break;
}
string query = String.Format(@"select contract.new_hindvcontractid,
contract.new_contractnumber,
hourlypricing.new_name as hourlypricingname,
Contract.new_finalprice,
Contract.CreatedOn,
Contract.new_contractmonth,
contract.new_shift,
Isnull(appointment.userrate,0) as userrate,
appointment.nextappointment,
Convert(date, contract.new_contractstartdate) as new_contractstartdate,
contract.new_latitude,
contract.new_longitude,
Isnull({0}('new_contractmonth','new_HIndvContract',Contract.new_contractmonth),{1}('new_contractmonth','new_HIndvContract',Contract.new_contractmonth) ) as durationname,
Contract.statuscode,
Isnull({2}('statuscode','new_HIndvContract',Contract.statuscode),{3}('statuscode','new_HIndvContract',Contract.statuscode) ) as statusname
-- contract.new_HIndivClintnameName,
--contract.new_HIndivClintname,contract.new_cityName
--,contract.new_city,contract.new_districtName ,contract.new_district,
--contract.new_discount_def,contract.new_totalprice_def,contract.new_totalvisits_def,contract.new_monthvisits_def,
--contract.new_contractmonth,contract.new_selecteddays,Contract.new_selectedshifts,Contract.new_visitprice_def,
--hourlypricing.new_nationalityName,
--hourlypricing.new_hourlypricingId,hourlypricing.new_shift
from new_HIndvContract contract inner join new_hourlypricing hourlypricing on contract.new_houlrypricing=hourlypricing.new_hourlypricingId
left outer join (
select new_hourlyappointmentBase.new_servicecontractperhour,
IIF( Count(new_hourlyappointmentBase.new_rate) > 0, ROUND( Sum(Isnull(new_hourlyappointmentBase.new_rate,0)) / Count(new_hourlyappointmentBase.new_rate), 0) , null) as userrate,
max(new_hourlyappointmentBase.new_shiftstart) as nextAppointment
from new_hourlyappointmentBase
where new_hourlyappointmentBase.new_rate is not null and Convert (date, new_shiftstart) <= Convert(date,GetDate())
group by new_servicecontractperhour
) as appointment on new_servicecontractperhour= contract.new_HIndvContractId
where contract.new_HIndivClintname = '{4}' {5}
order by contract.new_ContractNumber desc", optionSetGetValFn, otherLangOptionSetGetValFn, optionSetGetValFn, otherLangOptionSetGetValFn, userId, statusCondition);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
return(dt.AsEnumerable().Select(dataRow => new ServiceContractPerHour(dataRow, Language)));
//List<ServiceContractPerHour> List = new List<ServiceContractPerHour>();
//string ContractIds = "";
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// List.Add(new ServiceContractPerHour()
// {
// ContractId = dt.Rows[i][0].ToString(),
// ContractNum = dt.Rows[i][1].ToString(),
// Customer = dt.Rows[i][2].ToString(),
// CustomerId = dt.Rows[i][3].ToString(),
// City = dt.Rows[i][4].ToString(),
// District = dt.Rows[i]["new_districtName"].ToString(),
// Nationality = dt.Rows[i]["new_nationalityName"].ToString(),
// HourlyPricing = new HourlyPricing()
// {
// Discount = dt.Rows[i]["new_discount_def"].ToString(),
// TotalPrice = dt.Rows[i]["new_totalprice_def"].ToString(),
// TotalVisit = dt.Rows[i]["new_totalvisits_def"].ToString(),
// MonthVisits = dt.Rows[i][10].ToString(),
// NoOfMonths = dt.Rows[i]["new_contractmonth"].ToString(),
// VisitPrice = dt.Rows[i]["new_visitprice_def"].ToString(),
// NationalityName = dt.Rows[i]["new_nationalityName"].ToString(),
// hourlypricingId = dt.Rows[i][17].ToString(),
// Name = dt.Rows[i]["new_name"].ToString(),
// TotalbeforeDiscount = ((decimal.Parse(dt.Rows[i]["new_visitprice_def"].ToString()) * int.Parse(dt.Rows[i]["new_monthvisits_def"].ToString()) * int.Parse(dt.Rows[i]["new_totalvisits_def"].ToString()) * 4).ToString())
// },
// SelectedDays = dt.Rows[i]["new_selecteddays"].ToString(),
// Shift = dt.Rows[i]["new_shift"].ToString() == "False" ? "Morning" : "Evening",
// HourlyAppointments = new List<HourlyAppointment>()
// });
// ContractIds += "'" + dt.Rows[i][0].ToString() + "'";
// if (i != dt.Rows.Count - 1)
// ContractIds += ",";
//}
//return List;
}
public IEnumerable <string> GetDays(RequestHourlyPricing requestHourlyPricing, DayShifts Shift, int countOfDays, Promotion promotion)
{
requestHourlyPricing.ContractStartDate = requestHourlyPricing.ContractStartDate.Replace('/', '-');
DateTime startDate;
try
{
startDate = DateTime.ParseExact(requestHourlyPricing.ContractStartDate, "dd-MM-yyyy", CultureInfo.InvariantCulture);
}
catch (Exception ex)
{
startDate = DateTime.ParseExact(requestHourlyPricing.ContractStartDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);
}
var totalVisits = requestHourlyPricing.ContractDuration * requestHourlyPricing.Weeklyvisits;
var extraVisits = (promotion.FreeVisitsFactor ?? 0) == 0 ? 0 : Math.Truncate((decimal)totalVisits / promotion.FreeVisitsFactor.Value);
var totalPlusExtraVisits = totalVisits + extraVisits;
DateTime EndDate;
var contractDurationAfterPromotionInWeeks = (int)Math.Ceiling(totalPlusExtraVisits / requestHourlyPricing.Weeklyvisits);
if (contractDurationAfterPromotionInWeeks <= 3)
{
EndDate = startDate.AddDays(6 * contractDurationAfterPromotionInWeeks);
}
else
{
EndDate = startDate.AddDays(7 * contractDurationAfterPromotionInWeeks);
EndDate = EndDate.AddDays(-1);
}
string shift = Shift.ToString();
#region commented
/* string sql = @"Select distinct DayName from HourContractShifts('@ContractStartDate','@ContractEndDate','@districtid','@ShiftName',4)
* where nationalityId='@nationality'
* group by HourContractShifts.new_resourcecode,DayName
* having COUNT(*)>=@visitsCount order by DayName";
*
*
*
*
*
*
*
*
*
* //
* sql = sql.Replace("@ContractStartDate", startDate.Date.ToString());
* sql = sql.Replace("@ContractEndDate", EndDate.Date.ToString());
* sql = sql.Replace("@districtid", DistrictId);
* sql = sql.Replace("@ShiftName", shift);
* sql = sql.Replace("@nationality", NationalityId);
* if (months!=0)
* sql = sql.Replace("@visitsCount", (4 * months).ToString());
* else
* sql = sql.Replace("@visitsCount", (countofdays).ToString());
*
* DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
*
* string sql2 = @"select new_days from new_district where new_districtId='@id'";
* sql2 = sql2.Replace("@id", DistrictId);
* DataTable dtdistrict = CRMAccessDB.SelectQ(sql2).Tables[0];
*/
#endregion
// string sql = @"
//with allSihfts as (
// select ShiftFrom, nationalityId,dayname,Count(distinct new_hourresourceId) SeatNo,count(distinct new_employeeid) as NoOfResources from
//HourContractShiftsV02('@MinDate','@MaxDate','@districtid','@ShiftName','@nationality',@hoursCount,'@cityid')
// --where Dayname in ('Sunday')
// group by dayname,shiftfrom,nationalityId
// having Count(distinct new_hourresourceId)>=@EmpCount and count(distinct new_employeeid)>=@EmpCount
//)
//select nationalityId,dayname,count(dayname) counts from allSihfts
//group by nationalityId,dayname
//having count(dayname) >=@visitsCount";
string sql = @"exec dbo.HourContractShifts_ValidationV01
'@MinDate','@MaxDate','@districtid','@ShiftName','@nationality',@hoursCount,'@cityid' , @empCount, @visitsCount
";
//sql = sql.Replace("@visitsCount", (countOfDays / requestHourlyPricing.Weeklyvisits).ToString());
sql = sql.Replace("@visitsCount", (contractDurationAfterPromotionInWeeks).ToString());
sql = sql.Replace("@empCount", (requestHourlyPricing.Empcount).ToString());
sql = sql.Replace("@hoursCount", requestHourlyPricing.HoursCount.ToString());
sql = sql.Replace("@cityid", requestHourlyPricing.CityId);
sql = sql.Replace("@MinDate", startDate.AddHours(3).Date.ToString("MM/dd/yyyy"));
sql = sql.Replace("@MaxDate", EndDate.Date.ToString("MM/dd/yyyy"));
sql = sql.Replace("@districtid", requestHourlyPricing.DistrictId);
//if (shift == "صباحي") ShiftName = "Morning";
//if (ShiftName == "مسائي") ShiftName = "Evening";
sql = sql.Replace("@ShiftName", shift);
sql = sql.Replace("@nationality", requestHourlyPricing.NationalityId);
// SqlCommand CMD = new SqlCommand(sql);
System.Data.DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
string avaDays = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (avaDays.IndexOf(dt.Rows[i]["DayName"].ToString()) == -1)
{
avaDays = avaDays + dt.Rows[i]["DayName"] + ",";
}
}
if (!string.IsNullOrEmpty(avaDays))
{
avaDays = avaDays.Remove(avaDays.Length - 1);
}
List <string> DaysOfWeek = avaDays.Split(',').ToList();
//Avilable Days List
//List<string> List = new List<string>();
//for (int i = 0; i < dt.Rows.Count; i++) List.Add(dt.Rows[i][0].ToString());
return(DaysOfWeek);
}
public string Create(string Category, string Type, string Description, string CustomerNo, string HouseMadeId, string ContractId, int who = 1)
{
///TODO 1- Get Customer DATA
string sql = @"select contact.MobilePhone,contact.FullName,contact.YomiFullName from contact
where contact.ContactId='@id'";
sql = sql.Replace("@id", CustomerNo);
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
///TODO 1- Get Complaints Categories
///new_contracttype
///1-Bussiness Sector
///2-Indiv Sector
///4-HIndiv Sector
///TODO Categories
///new_problemcase
///100000005- Others
///6-تثبيت خدامة
///7-الغاء عقد
///8-تجديد عقد
///10-شكوى من خدامة
Entity CutomerServices = new Entity("new_csindvsector");
if (!string.IsNullOrEmpty(Category))
{
CutomerServices["new_contracttype"] = new OptionSetValue(int.Parse(Category));
}
if (!string.IsNullOrEmpty(Type))
{
CutomerServices["new_problemcase"] = new OptionSetValue(int.Parse(Type));
}
if (who <= 1)
{
CutomerServices["new_compalinsource"] = new OptionSetValue(who + 1);
}
if (!string.IsNullOrEmpty(ContractId))
{
CutomerServices["new_cshindivcontractid"] = new EntityReference("new_hindvcontract", new Guid(ContractId.ToString()));
}
string Complaintmessage = "";
//تثبيت خدامة
#region شكوى من خدامة او تثبيت خدامة
if (Type == "6" || Type == "10")
{
string sqlhousemade = @"select new_Employee.new_EmpIdNumber Code,new_Employee.new_name as Name,new_employee.new_nationalityIdName Nationality
,new_Employee.new_IDNumber Iqama
FROM new_Employee
where new_EmployeeId='@id'";
sqlhousemade = sqlhousemade.Replace("@id", HouseMadeId);
DataTable dthousemade = CRMAccessDB.SelectQ(sqlhousemade).Tables[0];
if (Type == "6")
{
Complaintmessage = "طلب تثبيت الخدامة /";
}
else
{
Complaintmessage = "شكوى الخدامة /";
}
Complaintmessage += dthousemade.Rows[0]["Name"].ToString();
Complaintmessage += " ورقم اقامتها : ";
Complaintmessage += dthousemade.Rows[0]["Iqama"].ToString();
Complaintmessage += " ورقم الوظيفى : ";
Complaintmessage += dthousemade.Rows[0]["Code"].ToString();
Complaintmessage += " وجنسية : ";
Complaintmessage += dthousemade.Rows[0]["Nationality"].ToString();
Complaintmessage += " ";
}
#endregion
Complaintmessage += " ";
Complaintmessage += Description;
CutomerServices["new_problemdetails"] = Complaintmessage;
Guid id = GlobalCode.Service.Create(CutomerServices);
return(id.ToString());
}
private string CreateContract(string CustomerNo, string NationalityId, string HourlyPricingId, string CityId, bool Shift, string DistrictId, string SelectedDays, string ContractStartDate, string lat, string lang, string MonthsCount, int VisitCount, string TotalPrice, int HoursCount, string Discount, string MonthelyPrice, int who = 1)
{
//Random generator = new Random();
String r = new Random().Next(0, 1000000).ToString("D6");
ContractStartDate = ContractStartDate.Replace('/', '-');
Entity contract = new Entity("new_hindvcontract");
contract["new_hindivclintname"] = new EntityReference("contact", new Guid(CustomerNo.ToString()));
contract["new_nationality"] = new EntityReference("new_country", new Guid(NationalityId));
contract["new_houlrypricing"] = new EntityReference("new_hourlypricing", new Guid(HourlyPricingId));
Entity hourlyPricing = GlobalCode.Service.Retrieve("new_hourlypricing", new Guid(HourlyPricingId), new ColumnSet(true));
contract["new_visitprice_def"] = int.Parse(MathNumber.RoundDeciamlToInt(hourlyPricing["new_hourprice"].ToString())) * HoursCount;
contract["new_visittotalprice"] = Decimal.Parse(hourlyPricing["new_hourprice"].ToString()) * HoursCount;
int months = int.Parse(MonthsCount);
contract["new_visitcount_def"] = SelectedDays.Split(',').Count();
contract["new_monthvisits_def"] = VisitCount;
if (months != 0)
{
contract["new_monthvisits_def"] = (VisitCount * 4);
}
if (months != 0)
{
contract["new_contractmonth"] = months;
}
//else
// contract["new_contractmonth"] = 13;
contract["new_totalvisits_def"] = VisitCount;
if (months != 0)
{
contract["new_totalvisits_def"] = VisitCount * 4 * months;
}
//hourlyPricing["new_totalvisits"];
if (who == 1)
{
contract["new_contractsource"] = new OptionSetValue(who);
}
else
{
contract["new_contractsource"] = new OptionSetValue(2);
}
contract["new_hindivcount"] = HoursCount;
contract["new_contractmonth"] = new OptionSetValue(months);
contract["new_weeklyvisits"] = new OptionSetValue(VisitCount);
contract["new_discount_def"] = decimal.Parse(Discount);
// Math.Round((decimal.Parse(a.TotalbeforeDiscount) - (decimal.Parse(a.TotalbeforeDiscount) * decimal.Parse(Discount) / 100)), 2, MidpointRounding.AwayFromZero).ToString().Replace(".00", "");
contract["new_totalprice_def"] = int.Parse(Math.Round(decimal.Parse(TotalPrice), 0, MidpointRounding.AwayFromZero).ToString());
contract["new_new_contractdate"] = DateTime.Now;
contract["new_city"] = new EntityReference("new_city", new Guid(CityId));
contract["new_district"] = new EntityReference("new_district", new Guid(DistrictId));
contract["new_shift"] = Shift == true ? false : true;
contract["new_contractconfirm"] = false;
contract["statuscode"] = new OptionSetValue(100000004);
contract["new_contractstartdate"] = DateTime.ParseExact(ContractStartDate, "dd-MM-yyyy", CultureInfo.InvariantCulture);
contract["new_customerdays"] = SelectedDays;
// contract["new_selecteddays"] = SelectedDays;
contract["new_latitude"] = lat;
contract["new_longitude"] = lang;
contract["new_paymentcode"] = r;
contract["new_mapurl"] = "http://maps.google.com/maps?q=" + lat + "," + lang + "&z=15";
Guid contractId = GlobalCode.Service.Create(contract);
if (!string.IsNullOrEmpty(contractId.ToString()))
{
//Send SMS To confirm
try
{
string contsql = @"
select new_HIndvContract.new_ContractNumber ,Contact.FirstName from new_HIndvContract,Contact
where Contact.ContactId =new_HIndvContract.new_HIndivClintname and
new_HIndvContract.new_HIndvContractId='@id'";
contsql = contsql.Replace("@id", contractId.ToString());
System.Data.DataTable dtcontract = CRMAccessDB.SelectQ(contsql).Tables[0];
string Sql = @"select MobilePhone,new_deviceid from Contact where ContactId='@id' ";
Sql = Sql.Replace("@id", CustomerNo);
System.Data.DataTable dt = CRMAccessDB.SelectQ(Sql).Tables[0];
//send Notification
string result = SendNotifications.SendNotify("," + CustomerNo, "عزيزى العميل /" + dtcontract.Rows[0]["FirstName"].ToString() + " " + "شكرا لاختياركم شركة ناس سيتم التواصل معكم قريبا لتاكيد الموعد للعقد رقم " + dtcontract.Rows[0]["new_ContractNumber"].ToString());
string body = "عزيزى العميل /" + dtcontract.Rows[0]["FirstName"].ToString() + " " + "شكرا لاختياركم شركة ناس سيتم التواصل معكم قريبا لتاكيد الموعد للعقد رقم " + dtcontract.Rows[0]["new_ContractNumber"].ToString();
AppleNotifications.SendPushNotification(dt.Rows[0]["new_deviceid"].ToString(), body, "شركة ناس للاستقدام");
//Send To SMS
/* Entity SMS = new Entity("new_smsns");
* string UserName = ConfigurationManager.AppSettings["SMSUserName"];
* string SMSPassword = ConfigurationManager.AppSettings["SMSPassword"];
* string TagName = ConfigurationManager.AppSettings["TagName"];
* SMSRef.SMSServiceSoapClient sms = new SMSRef.SMSServiceSoapClient();
* string MobileNumber = dt.Rows[0][0].ToString();
* string Message = "https://nasmanpower.com/HourlyServiceContract/Maps.aspx?id=" + contractId + " برجاء تاكيد الموقع باستخدام الرابط المرسل . شكرا لاستخدامكم شركة ناس ";
* string res = sms.SendBulkSMS(UserName, SMSPassword, TagName, MobileNumber, Message);*/
}
catch (Exception)
{
return(contractId.ToString());
}
}
return(contractId.ToString());
}