public IDictionary <string, string> doFindInsuranceCompanyAll() { BaseDao <SalesPerson> baseDao = DaoFactory <SalesPerson> .CreateBaseDao(typeof(SalesPerson)); string sql = "SELECT VSGSDM AS Code,VSGSMC AS Name FROM SJVDTALIB.VSM29 INSURANCE_COMPANY WHERE ZMDWDM='08'"; return(baseDao.FindAllDic(sql)); }
public DealerUser doGetDealerUserInfo(string username) { BaseDao <DealerUser> baseDao = DaoFactory <DealerUser> .CreateBaseDao(typeof(DealerUser)); string sql = "SELECT ISCZXM AS Username,EBPSWD AS Password,ISRYXM AS EmployeeName,ISKHDM AS DealerCode,ISFXDM AS RegionCode,ZMCZJB AS RightLevel " + "FROM SJVDTALIB.ISM01 DEALER_USER WHERE ISDWDM='08' AND ISJLZT='Y' AND ISCZXM=@USERNAME"; return(baseDao.FindByid(sql, "USERNAME", username)); }
public DealerStockVehicleInfo doFindDealerStockVehicleInfoById(string vin) { BaseDao <DealerStockVehicleInfo> baseDao = DaoFactory <DealerStockVehicleInfo> .CreateBaseDao(typeof(DealerStockVehicleInfo)); string sql = "SELECT CYVINM AS Vin,XSFDJH AS EngineNo,XSDPHM AS ChassisNo,ZMKHDM AS DealerCode,VTCLZT AS VehicleStatus,VTCLSX AS VehicleProperties," + "GET_DATE_STRING(VTRKRQ) AS VehicleInStockDate,GET_DATE_STRING(VTOKRQ) AS VehicleOutStockDate FROM SJVDTALIB.ITM02 DEALER_VEHICLE WHERE ZMDWDM='08' AND CYVINM=@VIN"; return(baseDao.FindByid(sql, "VIN", vin)); }
public List <SalesPerson> doFindSalesPersonAll() { BaseDao <SalesPerson> baseDao = DaoFactory <SalesPerson> .CreateBaseDao(typeof(SalesPerson)); string sql = "SELECT ZMKHDM AS DealerCode,VSXSRY AS SalesPersonCode,SALES_PERSON.VSXSYC AS SalesPersonName," + "SALES_PERSON.VTZJHM AS CertificateNo FROM SJVDTALIB.ISM40 SALES_PERSON WHERE ZMDWDM='08'"; return(baseDao.FindAll(sql)); }
public List <Dealer> doFindDealerAll() { BaseDao <Dealer> baseDao = DaoFactory <Dealer> .CreateBaseDao(typeof(Dealer)); string sql = "SELECT DEALER.ZMKHDM AS DealerCode,DEALER.ZMKHMC AS DealerName,DEALER.ZMNDDM AS NetCode,EXTEND.JSFXDM AS RegionCode FROM SJVDTALIB.XTM16 DEALER,SJVDTALIB.ISM02 EXTEND " + "WHERE DEALER.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND DEALER.ZMKHDM=EXTEND.ZMKHDM"; return(baseDao.FindAll(sql)); }
public List <Color> doFindColorAll() { BaseDao <Color> baseDao = DaoFactory <Color> .CreateBaseDao(typeof(Color)); string sql = "SELECT XSYSDM AS ColorCode,XSYSMC AS ColorName,'' AS ModelCode FROM SJVDTALIB.XSM04 COLOR WHERE COLOR.ZMDWDM='08'"; return(baseDao.FindAll(sql)); //return baseDao.FindAll("SELECT VW_COLOR.XSYSDM AS VwColorCode,BSYSDM AS ColorCode,XSYSMC AS ColorName,'' AS ModelCode " + //"FROM SJVDTALIB.XSM04 VW_COLOR,SJVDTALIB.KCM14 COLOR WHERE VW_COLOR.ZMDWDM='08' AND COLOR.ZMDWDM='08' AND VW_COLOR.XSYSDM=COLOR.XSYSDM"); }
public List <County> doFindCountyAll() { BaseDao <County> baseDao = DaoFactory <County> .CreateBaseDao(typeof(County)); string sql = "SELECT COUNTY.VSXJDM AS CountyCode,COUNTY.VSXJMC AS CountyName,CITY.VSDSDM AS CityCode,CITY.VSDSMC AS CityName," + "PROVINCE.VSSSDM AS ProvinceCode,PROVINCE.VSSSMC AS ProvinceName FROM SJVDTALIB.ISM10 COUNTY,SJVDTALIB.ISM09 CITY,SJVDTALIB.ISM08 PROVINCE " + "WHERE COUNTY.ZMDWDM='08' AND CITY.ZMDWDM='08' AND PROVINCE.ZMDWDM='08' AND COUNTY.VSDSDM=CITY.VSDSDM AND CITY.VSSSDM=PROVINCE.VSSSDM"; return(baseDao.FindAll(sql)); }
public List <ModelGroup> doFindModelGroupAll() { //log.Info("doFindModelPrAll begin"); getLogger(this).Info("doFindModelGroupAll begin"); BaseDao <ModelGroup> baseDao = DaoFactory <ModelGroup> .CreateBaseDao(typeof(ModelGroup)); List <ModelGroup> list = baseDao.FindAll("SELECT XSCLDM AS ModelGroupCode,BSCLDM AS ModelCode,CHAR(KCMDY1) AS ModelYear,KCVER1 AS ModelVersion,BSNSYM AS InteriorCode," + "CYCLP1 AS PrList, BSPNXH AS ModelPrNo FROM SJVDTALIB.KCM32 MODEL_GROUP WHERE ZMDWDM = '08' AND KCMDY1>=" + CfgReader.ModelGroupDataBeginYear); getLogger(this).Info("doFindModelGroupAll end"); return(list); }
public string doGetDealerCodeByUsername(string username) { BaseDao <string> baseDao = DaoFactory <string> .CreateBaseDao(typeof(string)); string sql = "SELECT ISKHDM FROM SJVDTALIB.ISM01 WHERE ISDWDM='08' AND ISJLZT='Y' AND ISCZXM=@USERNAME"; IDictionary <string, object> parms = new Dictionary <string, object>(); parms.Add("USERNAME", username); /*if (criteria.QueryCondition.Count > 0) * { * parms = new Dictionary<string, object>(); * if(!string.IsNullOrWhiteSpace(criteria.QueryCondition["ORDER_NO"])) * parms.Add("ORDER_NO", criteria.QueryCondition["ORDER_NO"]); * }*/ return(baseDao.nativeQuerySqlReturnString(sql, parms)); }
public RetailOrderQueryDetailsVo doFindRetailOrderById(string orderNo, string remarkFlag) { BaseDao <RetailOrderQueryDetailsVo> baseDao = DaoFactory <RetailOrderQueryDetailsVo> .CreateBaseDao(typeof(RetailOrderQueryDetailsVo)); string selectFields = string.Empty; string tableName = string.Empty; string condition = string.Empty; if (!string.IsNullOrWhiteSpace(remarkFlag) && "Y".Equals(remarkFlag)) { selectFields = "RETAIL.CYVINM AS Vin,RETAIL.VSLSDD AS OrderNo,GET_DATE_TIME_STRING(RETAIL.VSLSRQ,RETAIL.VSLSSJ) AS RetailDateTime," + "GET_DATE_STRING(RETAIL.VSLCRQ) AS OutStockDate,GET_DATE_STRING(RETAIL.DMFPRQ) AS InvoiceDate," + "RETAIL.BSCLDM AS Model,RETAIL.XSYSDM AS Color,RETAIL.BSYSDM AS Color1,'' AS ModelYear,'' AS ModelVersion,RETAIL.BSNSYM AS Interior,RETAIL.BSPNXH AS PrList," + "REMARK.VSYHMC AS CustomerName,RETAIL.VSXSLY AS SalesSource,RETAIL.VSDDZT AS OrderStatus,RETAIL.VSSFFJ AS Accessory,EXTEND.VSJLBZ AS Club," + "GET_DATE_STRING(RETAIL.VSDJRQ) AS CreateDate,'' AS EngineNo,RETAIL.DMFPLX AS InvoiceType,RETAIL.DMFPHM AS InvoiceNo,REMARK.VSLXDZ AS Address,REMARK.VSRYXB AS Gender," + "REMARK.VSXJDM AS County,RETAIL.DMKHLB AS CustomerType,RETAIL.DMCLYT AS VehiclePurpose,REMARK.VTZJLX AS CertificateType,REMARK.VTZJHM AS CertificateNo," + "REMARK.DMDHHM AS Telephone,REMARK.DMSJHM AS Mobile,REMARK.VSDJYJ AS Email,REMARK.DMLXRM AS Contact,REMARK.DMYZBM AS ZipCode,RETAIL.DMZWZW AS Duty," + "RETAIL.DMGSXZ AS CompanyProperty,RETAIL.DMCSHY AS CompanyIndustry,RETAIL.VSLSSX AS SalesType,RETAIL.VSLSKH AS ResellDealer," + "CHAR(RETAIL.VSLSDJ) AS Price,CHAR(RETAIL.VSLSSL) AS Quantity,RETAIL.VSXSRY AS SalesPerson,'' AS VehicleInStockDate,'' AS VehicleOutStockDate," + "RETAIL.VSLSBZ AS Remark,RETAIL.DMJFSZ AS UsePoint,CHAR(RETAIL.VSZFJE) AS PointAmount,RETAIL.VSPSLS AS PosFlowNo,RETAIL.VSLPCX AS Prize," + "EXTEND.VSSBCL AS ThreeGuarantees,EXTEND.XSCPBZ AS LicenseRemark,EXTEND.XSLSCP AS LicenseNo,REMARK.VSSFWS AS OutSource"; tableName = "SJVDTALIB.IST15 RETAIL,SJVDTALIB.VST17 EXTEND,SJVDTALIB.VST19 REMARK"; condition = "AND RETAIL.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND REMARK.ZMDWDM='08' AND RETAIL.VSLSDD=EXTEND.VSLSDD AND RETAIL.VSLSDD=REMARK.VSLSDD " + "AND RETAIL.VSLSDD=@ORDER_CODE"; } else { selectFields = "RETAIL.CYVINM AS Vin,RETAIL.VSLSDD AS OrderNo,GET_DATE_TIME_STRING(RETAIL.VSLSRQ,RETAIL.VSLSSJ) AS RetailDateTime," + "GET_DATE_STRING(RETAIL.VSLCRQ) AS OutStockDate,GET_DATE_STRING(RETAIL.DMFPRQ) AS InvoiceDate," + "RETAIL.BSCLDM AS Model,RETAIL.XSYSDM AS Color,RETAIL.BSYSDM AS Color1,'' AS ModelYear,'' AS ModelVersion,RETAIL.BSNSYM AS Interior,RETAIL.BSPNXH AS PrList," + "RETAIL.VSYHMC AS CustomerName,RETAIL.VSXSLY AS SalesSource,RETAIL.VSDDZT AS OrderStatus,RETAIL.VSSFFJ AS Accessory,EXTEND.VSJLBZ AS Club," + "GET_DATE_STRING(RETAIL.VSDJRQ) AS CreateDate,'' AS EngineNo,RETAIL.DMFPLX AS InvoiceType,RETAIL.DMFPHM AS InvoiceNo,RETAIL.VSLXDZ AS Address,RETAIL.VSRYXB AS Gender," + "RETAIL.VSXJDM AS County,RETAIL.DMKHLB AS CustomerType,RETAIL.DMCLYT AS VehiclePurpose,RETAIL.VTZJLX AS CertificateType,RETAIL.VTZJHM AS CertificateNo," + "RETAIL.DMDHHM AS Telephone,RETAIL.DMSJHM AS Mobile,RETAIL.VSDJYJ AS Email,RETAIL.DMLXRM AS Contact,RETAIL.DMYZBM AS ZipCode,RETAIL.DMZWZW AS Duty," + "RETAIL.DMGSXZ AS CompanyProperty,RETAIL.DMCSHY AS CompanyIndustry,RETAIL.VSLSSX AS SalesType,RETAIL.VSLSKH AS ResellDealer," + "CHAR(RETAIL.VSLSDJ) AS Price,CHAR(RETAIL.VSLSSL) AS Quantity,RETAIL.VSXSRY AS SalesPerson,'' AS VehicleInStockDate,'' AS VehicleOutStockDate," + "RETAIL.VSLSBZ AS Remark,RETAIL.DMJFSZ AS UsePoint,CHAR(RETAIL.VSZFJE) AS PointAmount,RETAIL.VSPSLS AS PosFlowNo,RETAIL.VSLPCX AS Prize," + "EXTEND.VSSBCL AS ThreeGuarantees,EXTEND.XSCPBZ AS LicenseRemark,EXTEND.XSLSCP AS LicenseNo,EXTEND.VSSFWS AS OutSource"; tableName = "SJVDTALIB.IST15 RETAIL,SJVDTALIB.VST17 EXTEND"; condition = "AND RETAIL.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND RETAIL.VSLSDD=EXTEND.VSLSDD " + "AND RETAIL.VSLSDD=@ORDER_CODE"; } string sql = DB2Helper.GetSql(selectFields, tableName, condition); return(baseDao.FindByid(sql, "ORDER_NO", orderNo)); }
public List <Interior> doFindModelInterior(string modelCode, string interior) { BaseDao <Interior> baseDao = DaoFactory <Interior> .CreateBaseDao(typeof(Interior)); IDictionary <string, object> parms = new Dictionary <string, object>(); string sql = "SELECT BSNSYM AS InteriorCode,BSNSJC AS InteriorName,'' AS ModelCode FROM SJVDTALIB.KCM10 INTERIOR WHERE ZMDWDM='08'"; /*if (!string.IsNullOrWhiteSpace(modelCode)) * { * sql = sql + " AND INTERIOR_CODE=@INTERIOR_CODE"; * parms.Add("INTERIOR_CODE", modelCode); * }*/ if (!string.IsNullOrWhiteSpace(interior)) { sql = sql + " AND BSNSYM=@INTERIOR_CODE"; parms.Add("INTERIOR_CODE", interior); } return(baseDao.nativeQuerySql(sql, parms)); }
public List <Color> doFindModelColor(string modelCode, string color) { BaseDao <Color> baseDao = DaoFactory <Color> .CreateBaseDao(typeof(Color)); IDictionary <string, object> parms = new Dictionary <string, object>(); string sql = "SELECT XSYSDM AS ColorCode,XSYSMC AS ColorName,'' AS ModelCode FROM SJVDTALIB.XSM04 COLOR WHERE ZMDWDM='08'"; /*if (!string.IsNullOrWhiteSpace(modelCode)) * { * sql = sql + " AND MODEL_CODE=@MODEL_CODE"; * parms.Add("MODEL_CODE", modelCode); * }*/ if (!string.IsNullOrWhiteSpace(color)) { sql = sql + " AND XSYSDM=@COLOR_CODE"; parms.Add("COLOR_CODE", color); } return(baseDao.nativeQuerySql(sql, parms)); }
public List <Interior> doFindInteriorAll() { BaseDao <Interior> baseDao = DaoFactory <Interior> .CreateBaseDao(typeof(Interior)); return(baseDao.FindAll("SELECT BSNSYM AS InteriorCode, BSNSJC AS InteriorName, '' AS ModelCode FROM SJVDTALIB.KCM10 INTERIOR WHERE ZMDWDM = '08'")); }
public PageResult <RetailOrderQueryListVo> doFindRetailOrderByCondition(QueryCriteria criteria) { BaseDao <RetailOrderQueryListVo> baseDao = DaoFactory <RetailOrderQueryListVo> .CreateBaseDao(typeof(RetailOrderQueryListVo)); string selectFields = string.Empty; string tableName = string.Empty; string condition = string.Empty; if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["remarkFlag"]) && "Y".Equals(criteria.QueryCondition["remarkFlag"])) { selectFields = "RETAIL.CYVINM AS Vin,RETAIL.VSLSDD AS OrderNo,GET_DATE_TIME_STRING(RETAIL.VSLSRQ,RETAIL.VSLSSJ) AS RetailDateTime,GET_DATE_STRING(RETAIL.VSLCRQ) AS OutStockDate,GET_DATE_STRING(RETAIL.DMFPRQ) AS InvoiceDate," + "RETAIL.BSCLDM AS ModelCode,RETAIL.XSYSDM AS Color,RETAIL.BSYSDM AS Color1,'' AS ModelYear,'' AS ModelVersion,RETAIL.BSNSYM AS Interior,RETAIL.BSPNXH AS PrList," + "REMARK.VSYHMC AS CustomerName,RETAIL.VSXSLY AS SalesSource,RETAIL.VSDDZT AS OrderStatus,RETAIL.VSSFFJ AS Accessory,EXTEND.VSJLBZ AS Club"; tableName = "SJVDTALIB.IST15 RETAIL,SJVDTALIB.VST17 EXTEND,SJVDTALIB.VST19 REMARK"; condition = "AND RETAIL.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND REMARK.ZMDWDM='08' AND RETAIL.VSLSDD=EXTEND.VSLSDD AND RETAIL.VSLSDD=REMARK.VSLSDD " + "AND RETAIL.ZMKHDM=@DEALER_CODE AND RETAIL.ZMFXDM=@REGION_CODE "; if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["customerName"])) { condition += "AND REMARK.VSYHMC LIKE '%" + criteria.QueryCondition["customerName"] + "%' "; } } else { selectFields = "RETAIL.CYVINM AS Vin,RETAIL.VSLSDD AS OrderNo,GET_DATE_TIME_STRING(RETAIL.VSLSRQ,RETAIL.VSLSSJ) AS RetailDateTime,GET_DATE_STRING(RETAIL.VSLCRQ) AS OutStockDate,GET_DATE_STRING(RETAIL.DMFPRQ) AS InvoiceDate," + "RETAIL.BSCLDM AS ModelCode,RETAIL.XSYSDM AS Color,RETAIL.BSYSDM AS Color1,'' AS ModelYear,'' AS ModelVersion,RETAIL.BSNSYM AS Interior,RETAIL.BSPNXH AS PrList," + "RETAIL.VSYHMC AS CustomerName,RETAIL.VSXSLY AS SalesSource,RETAIL.VSDDZT AS OrderStatus,RETAIL.VSSFFJ AS Accessory,EXTEND.VSJLBZ AS Club"; tableName = "SJVDTALIB.IST15 RETAIL,SJVDTALIB.VST17 EXTEND"; condition = "AND RETAIL.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND RETAIL.VSLSDD=EXTEND.VSLSDD " + "AND RETAIL.ZMKHDM=@DEALER_CODE AND RETAIL.ZMFXDM=@REGION_CODE "; if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["customerName"])) { condition += "AND RETAIL.VSYHMC LIKE '%" + criteria.QueryCondition["customerName"] + "%' "; } } string orderFields = "RETAIL.VSLSDD"; Dictionary <string, object> parms = new Dictionary <string, object>(); parms.Add("DEALER_CODE", criteria.QueryCondition["dealerCode"]); //parms.Add("REGION_CODE", criteria.QueryCondition["regionCode"]); parms.Add("REGION_CODE", "9999999"); if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["modelCode"])) { condition += "AND RETAIL.BSCLDM=@MODEL_CODE "; parms.Add("MODEL_CODE", criteria.QueryCondition["modelCode"]); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["colorCode"])) { condition += "AND RETAIL.XSYSDM=@COLOR_CODE "; parms.Add("COLOR_CODE", criteria.QueryCondition["colorCode"]); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["interiorCode"])) { condition += "AND RETAIL.BSNSYM=@INTERIOR_CODE "; parms.Add("INTERIOR_CODE", criteria.QueryCondition["interiorCode"]); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["vin"])) { condition += "AND RETAIL.CYVINM=@VIN "; parms.Add("VIN", criteria.QueryCondition["vin"]); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["invoiceNo"])) { condition += "AND RETAIL.DMFPHM=@INVOICE_NO "; parms.Add("INVOICE_NO", criteria.QueryCondition["invoiceNo"]); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["salesSource"])) { condition += "AND RETAIL.VSXSLY=@SALES_SOURCE "; parms.Add("SALES_SOURCE", criteria.QueryCondition["salesSource"]); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["orderStatus"])) { condition += "AND RETAIL.VSDDZT=@ORDER_STATUS "; parms.Add("ORDER_STATUS", criteria.QueryCondition["orderStatus"]); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["outStockStatus"])) { condition += "AND VSPCCL=@OUTSTOCK_STATUS "; parms.Add("OUTSTOCK_STATUS", criteria.QueryCondition["outStockStatus"]); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["startDateCreate"])) { condition += "AND RETAIL.VSDJRQ>=@START_DATE_CREATE "; parms.Add("START_DATE_CREATE", long.Parse(criteria.QueryCondition["startDateCreate"].ToString().Replace("-", ""))); } if (!string.IsNullOrWhiteSpace(criteria.QueryCondition["endDateCreate"])) { condition += "AND RETAIL.VSDJRQ<=@END_DATE_CREATE "; parms.Add("END_DATE_CREATE", long.Parse(criteria.QueryCondition["endDateCreate"].ToString().Replace("-", ""))); } string sql = DB2Helper.GetPageSql(selectFields, orderFields, tableName, condition, criteria.StartIndex, criteria.PageSize); string sqlCount = DB2Helper.GetCountSql(tableName, condition); return(baseDao.nativeQuerySql(sql, sqlCount, parms, criteria.StartIndex, criteria.PageSize)); }
public List <RetailOrderQueryExportVo> doFindRetailOrderByCondition(NameValueCollection queryString) { BaseDao <RetailOrderQueryExportVo> baseDao = DaoFactory <RetailOrderQueryExportVo> .CreateBaseDao(typeof(RetailOrderQueryExportVo)); Thread.Sleep(5000); string selectFields = string.Empty; string tableName = string.Empty; string condition = string.Empty; if (!string.IsNullOrWhiteSpace(queryString["remarkFlag"]) && "Y".Equals(queryString["remarkFlag"])) { selectFields = "RETAIL.VSLSDD AS OrderNo,RETAIL.ZMFXDM AS Region,RETAIL.ZMKHDM AS DealerCode,'' AS DealerName,'' AS NetCode," + "GET_DATE_STRING(RETAIL.VSDJRQ) AS CreateDate,GET_TIME_STRING(RETAIL.VSDJRQ) AS CreateTime," + "GET_DATE_STRING(RETAIL.VSLSRQ) AS RetailDate,GET_TIME_STRING(RETAIL.VSLSSJ) AS RetailTime," + "RETAIL.DMFPHM AS InvoiceNo,RETAIL.BSCLDM AS ModelCode,'' AS ModelName,RETAIL.XSYSDM AS ColorCode,'' AS ColorName," + "RETAIL.BSNSYM AS InteriorCode,'' AS InteriorName,'' AS ModelYear,'' AS ModelVersion,RETAIL.BSPNXH AS PrList,RETAIL.VSLSSX AS SalesType,'' AS Series," + "CHAR(RETAIL.VSLSDJ) AS Price,REMARK.VSYHMC AS CustomerName,REMARK.DMLXRM AS Contact,REMARK.VSRYXB AS Gender,'' AS Provice,'' AS City,RETAIL.VSXJDM AS County," + "REMARK.VSLXDZ AS Address,REMARK.DMYZBM AS ZipCode,RETAIL.DMCLYT AS VehiclePurpose,REMARK.DMFPLX AS InvoiceType,RETAIL.DMKHLB AS CustomerType,RETAIL.DMGSXZ AS CompanyProperty," + "RETAIL.DMZWZW AS Duty,REMARK.DMDHHM AS Telephone,REMARK.DMSJHM AS Mobile,REMARK.VSDJYJ AS Email,REMARK.VTZJLX AS CertificateType,REMARK.VTZJHM AS CertificateNo," + "RETAIL.DMCSHY AS CompanyIndustry,RETAIL.VSXSRY AS SalesPerson,RETAIL.VSXSLY AS SalesSource,RETAIL.VSPCCL AS OutStockStatus,'' AS MatchedDate,RETAIL.VSDDZT AS OrderStatus," + "RETAIL.XSFDJH AS EngineNo,RETAIL.CYVINM AS Vin,CHAR(RETAIL.VSLSSL) AS Quantity,GET_DATE_STRING(RETAIL.VSZFRQ) AS DeleteDate,GET_DATE_STRING(RETAIL.VSLCRQ) AS OutStockDate," + "RETAIL.VSLSBZ AS Remark,RETAIL.VSLPCX AS Prize,GET_DATE_STRING(RETAIL.DMFPRQ) AS InvoiceDate,RETAIL.VSSFFJ AS Accessory," + "EXTEND1.VSYHMC AS Owner,EXTEND1.VSBBXR AS Insured,EXTEND1.VSGSDM AS InsuranceCompany,EXTEND1.SNSYBH AS InsuranceBillNo," + "GET_DATE_STRING(EXTEND1.VSQSRQ) AS InsuranceBeginDate,GET_DATE_STRING(EXTEND1.VSZZRQ) AS InsuranceEndDate," + "EXTEND.ZWZFDM AS PaymentType,EXTEND.ZWJJDM AS FinancialInstitution,EXTEND.ZWDLDM AS LoanType,EXTEND.ZWZFBZ AS PaymentRemark," + "EXTEND.VSRSZH AS UsedCar,EXTEND.VSCKLC AS LongStorage,EXTEND.VSJLBZ AS Club,EXTEND.VSSBCL AS ThreeGuarantees,EXTEND.VSSFWS AS OutSource"; tableName = "SJVDTALIB.IST15 RETAIL,SJVDTALIB.VST17 EXTEND,SJVDTALIB.VST16 EXTEND1,SJVDTALIB.VST19 REMARK"; condition = "AND RETAIL.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND EXTEND1.ZMDWDM='08' AND REMARK.ZMDWDM='08' " + "AND RETAIL.VSLSDD=EXTEND.VSLSDD AND RETAIL.VSLSDD=EXTEND1.VSLSDD AND RETAIL.VSLSDD=REMARK.VSLSDD "; if (!string.IsNullOrWhiteSpace(queryString["customerName"])) { condition += "AND REMARK.VSYHMC LIKE '%" + queryString["customerName"] + "%' "; } } else { selectFields = "RETAIL.VSLSDD AS OrderNo,RETAIL.ZMFXDM AS Region,RETAIL.ZMKHDM AS DealerCode,'' AS DealerName,'' AS NetCode," + "GET_DATE_STRING(RETAIL.VSDJRQ) AS CreateDate,GET_TIME_STRING(RETAIL.VSDJRQ) AS CreateTime," + "GET_DATE_STRING(RETAIL.VSLSRQ) AS RetailDate,GET_TIME_STRING(RETAIL.VSLSSJ) AS RetailTime," + "RETAIL.DMFPHM AS InvoiceNo,RETAIL.BSCLDM AS ModelCode,'' AS ModelName,RETAIL.XSYSDM AS ColorCode,'' AS ColorName," + "RETAIL.BSNSYM AS InteriorCode,'' AS InteriorName,'' AS ModelYear,'' AS ModelVersion,RETAIL.BSPNXH AS PrList,RETAIL.VSLSSX AS SalesType,'' AS Series," + "CHAR(RETAIL.VSLSDJ) AS Price,RETAIL.VSYHMC AS CustomerName,RETAIL.DMLXRM AS Contact,RETAIL.VSRYXB AS Gender,'' AS Provice,'' AS City,RETAIL.VSXJDM AS County," + "RETAIL.VSLXDZ AS Address,RETAIL.DMYZBM AS ZipCode,RETAIL.DMCLYT AS VehiclePurpose,RETAIL.DMFPLX AS InvoiceType,RETAIL.DMKHLB AS CustomerType,RETAIL.DMGSXZ AS CompanyProperty," + "RETAIL.DMZWZW AS Duty,RETAIL.DMDHHM AS Telephone,RETAIL.DMSJHM AS Mobile,RETAIL.VSDJYJ AS Email,RETAIL.VTZJLX AS CertificateType,RETAIL.VTZJHM AS CertificateNo," + "RETAIL.DMCSHY AS CompanyIndustry,RETAIL.VSXSRY AS SalesPerson,RETAIL.VSXSLY AS SalesSource,RETAIL.VSPCCL AS OutStockStatus,'' AS MatchedDate,RETAIL.VSDDZT AS OrderStatus," + "RETAIL.XSFDJH AS EngineNo,RETAIL.CYVINM AS Vin,CHAR(RETAIL.VSLSSL) AS Quantity,GET_DATE_STRING(RETAIL.VSZFRQ) AS DeleteDate,GET_DATE_STRING(RETAIL.VSLCRQ) AS OutStockDate," + "RETAIL.VSLSBZ AS Remark,RETAIL.VSLPCX AS Prize,GET_DATE_STRING(RETAIL.DMFPRQ) AS InvoiceDate,RETAIL.VSSFFJ AS Accessory," + "EXTEND1.VSYHMC AS Owner,EXTEND1.VSBBXR AS Insured,EXTEND1.VSGSDM AS InsuranceCompany,EXTEND1.SNSYBH AS InsuranceBillNo," + "GET_DATE_STRING(EXTEND1.VSQSRQ) AS InsuranceBeginDate,GET_DATE_STRING(EXTEND1.VSZZRQ) AS InsuranceEndDate," + "EXTEND.ZWZFDM AS PaymentType,EXTEND.ZWJJDM AS FinancialInstitution,EXTEND.ZWDLDM AS LoanType,EXTEND.ZWZFBZ AS PaymentRemark," + "EXTEND.VSRSZH AS UsedCar,EXTEND.VSCKLC AS LongStorage,EXTEND.VSJLBZ AS Club,EXTEND.VSSBCL AS ThreeGuarantees,EXTEND.VSSFWS AS OutSource"; tableName = "SJVDTALIB.IST15 RETAIL,SJVDTALIB.VST17 EXTEND,SJVDTALIB.VST16 EXTEND1"; condition = "AND RETAIL.ZMDWDM='08' AND EXTEND.ZMDWDM='08' AND EXTEND1.ZMDWDM='08' AND RETAIL.VSLSDD=EXTEND.VSLSDD AND RETAIL.VSLSDD=EXTEND1.VSLSDD "; if (!string.IsNullOrWhiteSpace(queryString["customerName"])) { condition += "AND RETAIL.VSYHMC LIKE '%" + queryString["customerName"] + "%' "; } } string orderFields = "RETAIL.VSLSDD"; Dictionary <string, object> parms = new Dictionary <string, object>(); parms.Add("DEALER_CODE", queryString["dealerCode"]); parms.Add("REGION_CODE", "9999999"); if (!string.IsNullOrWhiteSpace(queryString["modelCode"])) { condition += "AND RETAIL.BSCLDM=@MODEL_CODE "; parms.Add("MODEL_CODE", queryString["modelCode"]); } if (!string.IsNullOrWhiteSpace(queryString["colorCode"])) { condition += "AND RETAIL.XSYSDM=@COLOR_CODE "; parms.Add("COLOR_CODE", queryString["colorCode"]); } if (!string.IsNullOrWhiteSpace(queryString["interiorCode"])) { condition += "AND RETAIL.BSNSYM=@INTERIOR_CODE "; parms.Add("INTERIOR_CODE", queryString["interiorCode"]); } if (!string.IsNullOrWhiteSpace(queryString["vin"])) { condition += "AND RETAIL.CYVINM=@VIN "; parms.Add("VIN", queryString["vin"]); } if (!string.IsNullOrWhiteSpace(queryString["invoiceNo"])) { condition += "AND RETAIL.DMFPHM=@INVOICE_NO "; parms.Add("INVOICE_NO", queryString["invoiceNo"]); } if (!string.IsNullOrWhiteSpace(queryString["salesSource"])) { condition += "AND RETAIL.VSXSLY=@SALES_SOURCE "; parms.Add("SALES_SOURCE", queryString["salesSource"]); } if (!string.IsNullOrWhiteSpace(queryString["orderStatus"])) { condition += "AND RETAIL.VSDDZT=@ORDER_STATUS "; parms.Add("ORDER_STATUS", queryString["orderStatus"]); } if (!string.IsNullOrWhiteSpace(queryString["outStockStatus"])) { condition += "AND VSPCCL=@OUTSTOCK_STATUS "; parms.Add("OUTSTOCK_STATUS", queryString["outStockStatus"]); } if (!string.IsNullOrWhiteSpace(queryString["startDateCreate"])) { condition += "AND RETAIL.VSDJRQ>=@START_DATE_CREATE "; parms.Add("START_DATE_CREATE", long.Parse(queryString["startDateCreate"].ToString().Replace("-", ""))); } if (!string.IsNullOrWhiteSpace(queryString["endDateCreate"])) { condition += "AND RETAIL.VSDJRQ<=@END_DATE_CREATE "; parms.Add("END_DATE_CREATE", long.Parse(queryString["endDateCreate"].ToString().Replace("-", ""))); } string sql = DB2Helper.GetSql(selectFields, tableName, condition, orderFields); return(baseDao.nativeQuerySql(sql, parms)); }
public void CreateBaseDaoTest() { BaseDao <RetailOrderQueryListVo> baseDao = DaoFactory <RetailOrderQueryListVo> .CreateBaseDao(typeof(RetailOrderQueryListVo)); Assert.IsNotNull(baseDao); }
public List <Series> doFindSeriesAll() { BaseDao <Series> baseDao = DaoFactory <Series> .CreateBaseDao(typeof(Series)); return(baseDao.FindAll("SELECT XSCXFL AS SeriesCode,XSCXFC AS SeriesName FROM SJVDTALIB.XSM11 SERIES WHERE ZMDWDM='08'")); }
public List <Model> doFindModelAll() { BaseDao <Model> baseDao = DaoFactory <Model> .CreateBaseDao(typeof(Model)); return(baseDao.FindAll("SELECT BSCLDM AS ModelCode,XSCLMC AS ModelName,XSCXFL SeriesCode FROM SJVDTALIB.KCM09 MODEL WHERE ZMDWDM='08'")); }