示例#1
0
        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));
        }
示例#2
0
        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));
        }