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() })); }