public IEnumerable <IndivPricing> GetPricingByNationality(string id)
{
string SQL = @"select new_indvprice.new_indvpriceId,new_indvprice.new_pricename,
new_indvprice.new_nationalityName,new_indvprice.new_contractmonths,new_indvprice.new_monthlypaid,new_indvprice.new_periodamount
,new_indvprice.new_everymonth,new_indvprice.new_pricenumber,new_indvprice.new_pricetype,new_nationality,new_prepaid
from new_indvprice
where new_nationality='@id'";
SQL = SQL.Replace("@id", id);
DataTable dt = CRMAccessDB.SelectQ(SQL).Tables[0];
List <IndivPricing> List = new List <IndivPricing>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new IndivPricing()
{
Id = dt.Rows[i]["new_indvpriceId"].ToString(),
Name = dt.Rows[i]["new_pricename"].ToString(),
Number = dt.Rows[i]["new_pricenumber"].ToString(),
NationalityName = dt.Rows[i]["new_nationalityName"].ToString(),
TypeId = dt.Rows[i]["new_pricetype"].ToString(),
TypeName = OptionsController.GetName("new_indvprice", "new_pricetype", 1025, dt.Rows[i]["new_pricetype"].ToString()),
ContractMonths = MathNumber.RoundDeciaml(dt.Rows[i]["new_contractmonths"].ToString()),
PeriodAmount = MathNumber.RoundDeciaml(dt.Rows[i]["new_periodamount"].ToString()),
EveryMonth = MathNumber.RoundDeciaml(dt.Rows[i]["new_everymonth"].ToString()),
MonthelyPaid = MathNumber.RoundDeciaml(dt.Rows[i]["new_monthlypaid"].ToString()),
PrePaid = MathNumber.RoundDeciaml(dt.Rows[i]["new_monthlypaid"].ToString()),
});
}
return(List);;
}
public IEnumerable <OptionList> GetPricingNationalities()
{
string SQL = @"select distinct new_indvprice.new_nationalityName,new_nationality
from new_indvprice where new_indvprice.new_publishedonweb =1
";
/* and new_indvprice.new_professionName like '%سائق خاص%'
* and new_indvprice.new_professionName like '%عاملة منزلية%'*/
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]["new_nationality"].ToString(),
Name = dt.Rows[i]["new_nationalityName"].ToString(),
});
}
var index = List.FindIndex(x => x.Id.ToUpper() == "C9DA5D56-A54A-E311-8887-00155D010303");
if (index != -1)
{
var item = List[index];
List[index] = List[0];
List[0] = item;
}
return(List);
}
public IEnumerable <IndivPricing> GetIndivPrices(string nationalityId, string professionId)
{
string SQL = @"select distinct * from new_indvprice
where
new_nationality = '@nationalityId'
and new_profession = '@professionId' and statecode = 0 and new_forweb = 1";
SQL = SQL.Replace("@nationalityId", nationalityId);
SQL = SQL.Replace("@professionId", professionId);
DataTable dt = CRMAccessDB.SelectQ(SQL).Tables[0];
List <IndivPricing> List = new List <IndivPricing>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new IndivPricing()
{
Id = dt.Rows[i]["new_indvpriceId"].ToString(),
Name = dt.Rows[i]["new_pricename"].ToString(),
Number = dt.Rows[i]["new_pricenumber"].ToString(),
NationalityName = dt.Rows[i]["new_nationalityName"].ToString(),
TypeId = dt.Rows[i]["new_pricetype"].ToString(),
TypeName = OptionsController.GetName("new_indvprice", "new_pricetype", 1025, dt.Rows[i]["new_pricetype"].ToString()),
ContractMonths = MathNumber.RoundDeciaml(dt.Rows[i]["new_contractmonths"].ToString()),
PeriodAmount = MathNumber.RoundDeciaml(dt.Rows[i]["new_periodamount"].ToString()),
EveryMonth = MathNumber.RoundDeciaml(dt.Rows[i]["new_everymonth"].ToString()),
MonthelyPaid = MathNumber.RoundDeciaml(dt.Rows[i]["new_monthlypaid"].ToString()),
PrePaid = MathNumber.RoundDeciaml(dt.Rows[i]["new_monthlypaid"].ToString()),
NationalityId = nationalityId,
ProfessionId = professionId,
});
}
return(List);
}
public IEnumerable <Complaint> GetComplaintsDetails(string id, string status)
{
string Sql = @"select new_name,new_contracttype,new_problemcase,new_compalinsource
,new_cshindivcontractid,new_problemdetails ,new_HIndvContract.new_HIndivClintnameName,new_HIndvContract.new_HIndivClintname
,dateadd (hh,3,new_csindvsector.CreatedOn)
,CONVERT(VARCHAR(20),dateadd(hh, 3, new_csindvsector.CreatedOn ),103) as edate,FORMAT(CAST(dateadd(hh, 3, new_csindvsector.CreatedOn ) AS DATETIME),'hh:mm tt') as etime,new_csindvsector.statuscode
,new_HIndvContract.new_ContractNumber
from new_csindvsector,new_HIndvContract
where new_csindvsector.new_cshindivcontractid=new_HIndvContract.new_HIndvContractId
and new_HIndvContract.new_HIndivClintname='@id' and new_csindvsector.statuscode='@stat' order by new_csindvsector.CreatedOn desc";
Sql = Sql.Replace("@id", id);
Sql = Sql.Replace("@stat", status);
DataTable dt = CRMAccessDB.SelectQ(Sql).Tables[0];
List <Complaint> List = new List <Complaint>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List.Add(new Complaint {
Code = dt.Rows[i]["new_name"].ToString(),
Category = OptionsController.GetName("new_csindvsector", "new_contracttype", 1025, dt.Rows[i]["new_contracttype"].ToString()),
Type = OptionsController.GetName("new_csindvsector", "new_problemcase", 1025, dt.Rows[i]["new_problemcase"].ToString()),
Date = dt.Rows[i]["edate"].ToString(),
Time = dt.Rows[i]["etime"].ToString(),
Description = dt.Rows[i]["new_problemdetails"].ToString(),
CustomerName = dt.Rows[i]["new_HIndivClintnameName"].ToString(),
Status = OptionsController.GetName("new_csindvsector", "statuscode", 1025, dt.Rows[i]["statuscode"].ToString()),
ContractNumber = dt.Rows[i]["new_ContractNumber"].ToString(),
});
}
return(List);
}
public IEnumerable <HourlyPricing> GetHourlyPricingByNationalityAndShift(string NationalityId, DayShifts 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,new_shift from new_hourlypricingBase
Where new_nationality='@nationalityId' and new_shift=@shift";
int shifttype = (int)Shift;
sql = sql.Replace("@nationalityId", NationalityId);
sql = sql.Replace("@shift", shifttype.ToString());
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
List <HourlyPricing> List = dt.AsEnumerable().Select(dataRow => new HourlyPricing(dataRow)).ToList();
//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 CustomerTicket GetDalalTicketByNumber(string ticketNumber, UserLanguage lang)
{
string functionToGetProblemsName = lang == UserLanguage.Arabic ? "getOptionSetDisplay" : "getOptionSetDisplayen";
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,
contract.new_contractNumber as contractName,ticket.new_contactName,ticket.CreatedOn,ticket.new_contracttype,ticket.new_problemcase,
[dbo].[{0}]('{1}','{2}', ticket.new_problemcase) as new_problemcaseName,
[dbo].[{0}]('{3}','{2}', ticket.new_contracttype) as new_contracttypeName,
[dbo].[{0}]('{4}','{2}', ticket.statuscode) as statuscodeName
from new_csindvsector ticket
left outer join new_HIndvContract contract on ticket.new_indvcontractid= contract.new_HIndvContractId
left outer join contact
on ticket.new_contact = contact.ContactId
where ticket.new_name= '{5}'", functionToGetProblemsName, "new_problemcase", "new_csindvsector", "new_contracttype", "statuscode", ticketNumber);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
if (dt.Rows.Count == 0)
{
return(null);
}
var ticket = new CustomerTicket(dt.Rows[0]);
return(ticket);
}
public List <Nationality> GetAllNationlity(int lang = 0)
{
string sqlQuery = @"select
country.new_CountryId CountryId,
country.new_name CountryNameAr,
country.new_NameEnglish CountryNameEn
from new_CountryBase country
order by country.new_name";
DataTable dt = CRMAccessDB.SelectQ(sqlQuery).Tables[0];
List <Nationality> nationlitys = new List <Nationality>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (lang == 0)
{
nationlitys.Add(new Nationality {
CountryId = dt.Rows[i]["CountryId"].ToString(), Name = dt.Rows[i]["CountryNameAr"].ToString()
});
}
else
{
nationlitys.Add(new Nationality {
CountryId = dt.Rows[i]["CountryId"].ToString(), Name = dt.Rows[i]["CountryNameEn"].ToString()
});
}
}
return(nationlitys);
}
public IEnumerable <Nationality> GetDistNats(string id)
{
string sql;
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,new_carresource,new_district,new_district_carresource
where country.new_CountryId =hourPrice.new_nationality
and new_carresource.new_carresourceId=new_district_carresource.new_carresourceid and new_district.new_districtId=new_district_carresource.new_districtId
and new_district.new_districtId='@id' order by country.new_name" ;
sql = sql.Replace("@id", id);
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);
}
public IEnumerable <Lead> GetLeads(string sectorId, 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
from Lead cl
where cl.new_sector = {1}", functionToGetProblemsName, sectorId);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
return(dt.AsEnumerable().Select(dataRow => new Lead(dataRow)));
}
public IEnumerable <OptionList> GetUserContracts(string id, int type, int who = 1)
{
//قطاع الاعمال1
//قطاع افراد2
//عقود بالساعة4
if (type == 4)
{
string Sql = @" select distinct new_HIndvContract.new_HIndvContractId,'('+new_HIndvContract.new_ContractNumber+')/'+new_HIndvContract.new_houlrypricingName as name
from new_HIndvContract
where
new_HIndvContract.new_HIndivClintname='@id'" ;
Sql = Sql.Replace("@id", id);
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]["new_HIndvContractId"].ToString(), Name = dt.Rows[i]["name"].ToString()
});
}
return(List);
}
else
{
List <OptionList> List = new List <OptionList>();
return(List);
}
}
public IEnumerable <HourlyAppointment> GetHourlyAppointments(string contractId, UserLanguage lang)
{
string functionToGetProblemsName = lang == UserLanguage.Arabic ? "getOptionSetDisplay" : "getOptionSetDisplayen";
string SqlShifts = String.Format(@"select
new_hourlyappointmentId,
new_servicecontractperhour,
new_employeeName,
new_employee,
new_status,
[dbo].[{0}]('new_status','new_hourlyappointment', new_hourlyappointment.new_status) as statusName,
new_hourlyappointment.new_notes,
dateadd(hh,3,new_shiftend) as new_shiftend ,
dateadd(hh,3,new_shiftstart) as new_shiftstart ,
dateadd(hh,3,new_actualshiftstart) new_actualshiftstart,
dateadd(hh,3,new_actualshiftend) new_actualshiftend,
Isnull( new_rate,0) as new_rate,
new_carid,
new_caridName
from new_hourlyappointment inner join new_HIndvContract on new_HIndvContract.new_HIndvContractId = new_hourlyappointment.new_servicecontractperhour
where new_hourlyappointment.new_servicecontractperhour = '{1}'
order by new_shiftstart", functionToGetProblemsName, contractId);
var result = CRMAccessDB.SelectQ(SqlShifts).Tables[0].AsEnumerable().Select(dataRow => new HourlyAppointment(dataRow));
return(result);
}
private int UpdateEntity(string Query)
{
int result = CRMAccessDB.Update(Query);
return(result);
}
public IEnumerable <OptionList> GetUserHouseMades(string id)
{
string Sql = @" select distinct new_hourlyappointment.new_employeeName,new_hourlyappointment.new_employee
from new_hourlyappointment,new_HIndvContract
where
new_hourlyappointment.new_servicecontractperhour=new_HIndvContract.new_HIndvContractId
and new_hourlyappointment.new_employee is not null
and new_HIndvContract.new_HIndivClintname='@id'" ;
Sql = Sql.Replace("@id", id);
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]["new_employee"].ToString(), Name = dt.Rows[i]["new_employeeName"].ToString()
});
}
return(List);
}
public List <Profession> GetAllProfessions(int lang = 0)
{
string sqlQuery = @"select
distinct
profession.new_professionId ProfessionId,
profession.new_name professionNameAr,
profession.new_ProfessionEnglish professionNameEn
from new_profession profession
order by profession.new_name ";
DataTable dt = CRMAccessDB.SelectQ(sqlQuery).Tables[0];
List <Profession> professions = new List <Profession>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (lang == 0)
{
professions.Add(new Profession {
ProfessionId = dt.Rows[i]["ProfessionId"].ToString(), ProfessionName = dt.Rows[i]["professionNameAr"].ToString()
});
}
else
{
professions.Add(new Profession {
ProfessionId = dt.Rows[i]["ProfessionId"].ToString(), ProfessionName = dt.Rows[i]["professionNameEn"].ToString()
});
}
}
return(professions);
}
public List <City> GetAllCityBase(int lang = 0)
{
string sqlQuery = @" select
city.new_CityId cityId,
city.new_name cityNameAr,
city.new_englsihName cityNameEn
from new_City city order by city.new_name";
DataTable dt = CRMAccessDB.SelectQ(sqlQuery).Tables[0];
List <City> cities = new List <City>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (lang == 0)
{
cities.Add(new City {
CityId = dt.Rows[i]["cityId"].ToString(), Name = dt.Rows[i]["cityNameAr"].ToString()
});
}
else
{
cities.Add(new City {
CityId = dt.Rows[i]["cityId"].ToString(), Name = dt.Rows[i]["cityNameEn"].ToString()
});
}
}
return(cities);
}
public bool CheckContactIdNumber(string idNumber)
{
string SQL = @"SELECT TOP 1000 [ContactId] FROM [Abdal_MKH_MSCRM].[dbo].[ContactBase] where new_IdNumer = '@idNumber'";
SQL = SQL.Replace("@idNumber", idNumber);
return(CRMAccessDB.SelectQ(SQL).Tables[0].Rows.Count > 0 ? true : false);
}
public string GetLatestNotPaidContract(string crmUserId)
{
string sql = string.Format(@"select top 1 {0} from
{1} where new_hindivclintname = N'{2}' and statuscode = N'100000006'
order by createdon desc", CrmGuidFieldName, CrmEntityName, crmUserId);
return(Convert.ToString(CRMAccessDB.ExecuteScalar(sql)));
}
public bool ConfirmTerms(string contractId)
{
var sql = String.Format(@"update new_hindvcontract
set new_agreeonconditions=1
where new_HIndvContractId='{0}'", contractId);
var result = CRMAccessDB.ExecuteNonQuery(sql);
return(result > 0);
}
public IEnumerable <BaseQuickLookup> GetLookups_AvailableCities(UserLanguage language)
{
string displayField = (language == UserLanguage.Arabic ? "new_name" : "new_englsihName");
string sql = String.Format(@"SELECT distinct city.new_CityId CityId, city.{0} Name, city.versionnumber 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.{1} ", displayField, displayField);
return(CRMAccessDB.SelectQ(sql).Tables[0].AsEnumerable().Select(dataRow => new BaseQuickLookup(dataRow["CityId"].ToString(), dataRow["Name"].ToString())));
}
public IEnumerable <BaseQuickLookup> GetDalalContracts(string customerId)
{
var query = String.Format(@"Select new_HIndvContract.new_HIndvContractId , new_HIndvContract.new_ContractNumber
from new_HIndvContract
where new_HIndvContract.new_hindivclintname = '{0}'" , customerId);
var result = CRMAccessDB.SelectQ(query).Tables[0].AsEnumerable().Select(dataRow => new BaseQuickLookup(dataRow["new_HIndvContractId"].ToString(), dataRow["new_ContractNumber"].ToString()));
return(result);
}
public IEnumerable <HourlyPricing> Get(bool all, int?shift)
{
string sql = "";
if (all == false)
{
sql = @"SELECT hourPrice.new_hourlypricingId hourlypricingId,hourPrice.new_name name,hourPrice.new_visitcount visitcount,hourPrice.new_visitprice visitprice,
hourPrice.new_discount discount,hourPrice.new_hours [hours],hourPrice.new_noofmonths noofmonths,hourPrice.new_totalvisits totalvisit,
hourPrice.new_totalprice totalprice,hourPrice.new_monthvisits monthvisits,
CASE WHEN hourPrice.new_shift =0 then 'Morning' else 'Evening' END as [shift],
country.new_CountryId nationalityId,country.new_name nationalityname
from new_hourlypricingBase hourPrice,new_country country
Where hourPrice.new_nationality=country.new_CountryId and
hourPrice.new_nationality='fa0ef838-292f-e311-b3fd-00155d010303' and hourPrice.new_shift=@shift
order by country.new_CountryId,hourPrice.new_shift ";
}
else
{
sql = @"SELECT hourPrice.new_hourlypricingId hourlypricingId,hourPrice.new_name name,hourPrice.new_visitcount visitcount,hourPrice.new_visitprice visitprice,
hourPrice.new_discount discount,hourPrice.new_hours [hours],hourPrice.new_noofmonths noofmonths,hourPrice.new_totalvisits totalvisit,
hourPrice.new_totalprice totalprice,hourPrice.new_monthvisits monthvisits,
CASE WHEN hourPrice.new_shift =0 then 'Morning' else 'Evening' END as [shift],
country.new_CountryId nationalityId,country.new_name nationalityname
from new_hourlypricingBase hourPrice,new_country country
Where hourPrice.new_nationality=country.new_CountryId
and hourPrice.new_nationality !='fa0ef838-292f-e311-b3fd-00155d010303' and hourPrice.new_shift=@shift
order by country.new_CountryId,hourPrice.new_shift ";
}
sql = sql.Replace("@shift", shift.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()
{
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 HttpResponseMessage GetAllContracts()
{
string query = "select new_hindvcontractid , new_contractnumber ," +
"new_districtName from new_hindvcontract";
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
var result = dt.AsEnumerable().Select(dataRow => new ServiceContractPerHour(dataRow)).ToList();
return(OkResponse(result));
}
public HttpResponseMessage GetContractById(Guid id)
{
string query = string.Format("select new_hindvcontractid , new_contractnumber ," +
"new_districtName,new_cityName from new_hindvcontract where new_hindvcontractid= '{0}'",
id);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
var result = dt.AsEnumerable().Select(dataRow => new ServiceContractPerHour(dataRow)).ToList();
return(OkResponse(result));
}
//new_companysector
public static string GetName(string EntityName, string FieldName, int language, string Value)
{
string SQL = @"select Value from StringMap s inner join EntityLogicalView e on s.ObjectTypeCode = e.ObjectTypeCode
where e.Name = '@entityname' and s.AttributeName = '@optionname' and LangId=@lang
and s.AttributeValue='@value'";
SQL = SQL.Replace("@entityname", EntityName).Replace("@optionname", FieldName).Replace("@lang", language.ToString()).Replace("@value", Value);
DataTable dt = CRMAccessDB.SelectQ(SQL).Tables[0];
return dt.Rows[0]["Value"].ToString();
}
public List<OptionList> GetMonths(int lang = 0)
{
string sql = SqlQuery.Replace("@entityname", "new_HIndvContract").Replace("@optionname", "new_contractmonth")
.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.ToList();
}
public DomesticInvoice GetDomesticInvoiceDetails(string id, UserLanguage Lang)
{
// =================================================
//string[] columns = { CrmGuidFieldName, CrmDisplayFieldName, "new_indvcontractid", "new_paymentduedate" , "new_fromdate", "new_todate",
// "new_custamount", "new_totalamountwithvat", "new_paymenttype" , "new_ispaid", "new_customer"
//};
//var entity = GetCrmEntity(id, columns);
//if (entity == null) return null;
//return new DomesticInvoice(entity);
// ===================================================
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, Convert(date, new_fromdate) as new_fromdate ,
Convert(date, new_todate) as 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, new_indvpayment.new_invoicenodays
From new_indvpayment left outer join contact on contact.contactid = new_indvpayment.new_customer
Where new_indvpaymentid = '{0}'
", id, CrmEntityName, optionSetGetValFn, otherLangOptionSetGetValFn);
DataTable dt = CRMAccessDB.SelectQ(query).Tables[0];
if (dt.Rows.Count == 0)
{
return(null);
}
var invoice = new DomesticInvoice(dt.Rows[0]);
return(invoice);
}
//this Action for car application for users payment
public decimal AddPayment(string carid, int paymenttype, string amount, string datatime, string contractid, string paymentcode)
{
string sql = @"select new_totalprice_def,new_HIndvContractId from new_HIndvContract where new_ContractNumber='@contractid'";
sql = sql.Replace("@contractid", contractid);
DataTable dt = CRMAccessDB.SelectQ(sql).Tables[0];
Entity Receipt = new Entity("new_receiptvoucher");
Receipt["new_contracthourid"] = new EntityReference("new_hindvcontract", new Guid(dt.Rows[0][1].ToString()));
Receipt["new_sourcecar"] = new EntityReference("new_carresource", new Guid(carid));
if (paymenttype == 0)
{
Receipt["new_paymenttype"] = new OptionSetValue(6);
Receipt["new_refrencenumber"] = paymentcode;
}
else
{
Receipt["new_paymenttype"] = new OptionSetValue(3);
}
Receipt["new_amount"] = new Money(decimal.Parse(dt.Rows[0][0].ToString()));
Receipt["new_receiptdate"] = DateTime.ParseExact(datatime, "dd/MM/yyyy", null);
Guid Id = GlobalCode.Service.Create(Receipt);
string sqltotal = @"select isnull((select sum(new_amount) from new_receiptvoucher
where new_receiptvoucher.new_contracthourid='@id'),0) as paidamount from new_HIndvContract Contract ";
sqltotal = sqltotal.Replace("@id", dt.Rows[0][1].ToString());
DataTable dttable = CRMAccessDB.SelectQ(sqltotal).Tables[0];
// new_ispaid
if ((decimal.Parse(dt.Rows[0]["new_totalprice_def"].ToString()) - decimal.Parse(dttable.Rows[0][0].ToString())) <= 0)
{
Entity Contract = GlobalCode.Service.Retrieve("new_hindvcontract", new Guid(dt.Rows[0][1].ToString()), new ColumnSet(false));
Contract["new_ispaid"] = true;
Contract["new_contractconfirm"] = true;
GlobalCode.Service.Update(Contract);
}
return(decimal.Parse(dt.Rows[0]["new_totalprice_def"].ToString()) - decimal.Parse(dttable.Rows[0][0].ToString()));
}
public IEnumerable <BaseQuickLookup> GetLookups_DistrictNationalities(string districtId, UserLanguage language)
{
string displayField = (language == UserLanguage.Arabic ? "name" : "NameEnglish");
string 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,new_carresource,new_district,new_district_carresource
where country.new_CountryId =hourPrice.new_nationality
and new_carresource.new_carresourceId=new_district_carresource.new_carresourceid and new_district.new_districtId=new_district_carresource.new_districtId
and new_district.new_districtId='@id' order by country.new_name" ;
sql = sql.Replace("@id", districtId);
return(CRMAccessDB.SelectQ(sql).Tables[0].AsEnumerable().Select(dataRow => new BaseQuickLookup(dataRow["CountryId"].ToString(), dataRow[displayField].ToString())));
}
public IEnumerable <BaseQuickLookup> GetDalalServedEmployeesByCustomerId(string customerId)
{
var query = String.Format(@"Select new_hourlyappointment.new_employee , 'Iqama:'+new_employee.new_idnumber+'_'+ new_hourlyappointment.new_employeeName as new_employeeName
from new_hourlyappointment
inner join new_HIndvContract on new_hourlyappointment.new_servicecontractperhour = new_HIndvContract.new_HIndvContractId
inner join new_employee on new_hourlyappointment.new_employee=new_employee.new_employeeid
where
--new_hourlyappointment.new_status in ({0})
new_HIndvContract.new_hindivclintname = '{0}'" ,
string.Join(",", customerId));
var result = CRMAccessDB.SelectQ(query).Tables[0].AsEnumerable().Select(dataRow => new BaseQuickLookup(dataRow["new_employee"].ToString(), dataRow["new_employeeName"].ToString()));
return(result);
}
public IEnumerable <BaseQuickLookup> GetQuickLookup(string entityName, string idFieldName, string textFieldName, string otherTextFieldIfNull = null, string filterWhereCondition = "")
{
otherTextFieldIfNull = String.IsNullOrEmpty(otherTextFieldIfNull) ? textFieldName : otherTextFieldIfNull;
string query = String.Format(@"Select IsNull({0},{1}) as Text, {2} as Value
From {3} {4}" ,
textFieldName, otherTextFieldIfNull, idFieldName, entityName, filterWhereCondition);
var dt = CRMAccessDB.SelectQ(query).Tables[0];
return(dt.AsEnumerable().Select(row => new BaseQuickLookup()
{
Key = row["Value"].ToString(), Value = row["Text"].ToString()
}));
}
