private void GetQueryCondition() { _queryModel = new SummaryReportQueryModel(); if (comboBoxStore.SelectedIndex > 0) { _queryModel.StoreId = Guid.Parse(comboBoxStore.SelectedValue.ToString()); } else { _queryModel.StoreId = Guid.Empty; } _queryModel.Source = comboBoxSource.Text.Replace("-", "").Trim(); _queryModel.SN = textBoxSN.Text.Trim(); _queryModel.SNName = textBoxSNName.Text.Trim(); _queryModel.ReportType = checkBoxReportType.Checked ? 2 : 1; if (radioButtonCustomer.Checked) { _queryModel.StartTime = dateTimePickerStart.Value; _queryModel.FinishTime = dateTimePickerFinish.Value; } if (radioButtonDefine.Checked) { _queryModel.FinishTime = DateTime.Parse(DateTime.Now.ToString(TIME_FORMATFINISH)); Int32 addday = 0; if (_defineTimeDic.ContainsKey(comboBoxTime.Text)) { addday = _defineTimeDic[comboBoxTime.Text]; } _queryModel.StartTime = DateTime.Parse(DateTime.Now.AddDays(0 - addday).ToString(TIME_FORMATSTART)); } }
private void GetQueryCondition() { _queryModel = new SummaryReportQueryModel(); if (comboBoxStore.SelectedIndex > 0) { _queryModel.StoreId = Guid.Parse(comboBoxStore.SelectedValue.ToString()); } else { _queryModel.StoreId = Guid.Empty; } _queryModel.Source = comboBoxSource.Text.Replace("-","").Trim(); _queryModel.SN = textBoxSN.Text.Trim(); _queryModel.SNName = textBoxSNName.Text.Trim(); _queryModel.ReportType = checkBoxReportType.Checked ? 2 : 1; if (radioButtonCustomer.Checked) { _queryModel.StartTime = dateTimePickerStart.Value; _queryModel.FinishTime = dateTimePickerFinish.Value; } if (radioButtonDefine.Checked) { _queryModel.FinishTime = DateTime.Parse(DateTime.Now.ToString(TIME_FORMATFINISH)); Int32 addday = 0; if (_defineTimeDic.ContainsKey(comboBoxTime.Text)) { addday = _defineTimeDic[comboBoxTime.Text]; } _queryModel.StartTime = DateTime.Parse(DateTime.Now.AddDays(0 - addday).ToString(TIME_FORMATSTART)); } }
public HandlingResult Query(SummaryReportQueryModel queryModel) { HandlingResult result=new HandlingResult(); //SELECT SUM(NETPAY)AS NETPAY, SUM(PAYABLE) AS NETPAY, SUM(HANDLING) AS NETPAY, EXCSOURCE //FROM BUSINESS_TRANSFLOW //WHERE SN = '' AND EXCTIME BETWEEN '' AND '' //GROUP BY EXCSOURCE //SELECT EXCSOURCE, NETPAY, PAYABLE, HANDLING //FROM BUSINESS_TRANSFLOW //WHERE SN = '' AND EXCTIME BETWEEN '' AND '' //SELECT SUM(NETPAY) AS NETPAY, SUM(PAYABLE) AS NETPAY, SUM(HANDLING) AS NETPAY, EXCSOURCE, SN //FROM BUSINESS_TRANSFLOW //WHERE EXCTIME BETWEEN '' AND '' //GROUP BY SN, EXCSOURCE StringBuilder cntsqlbuilder = new StringBuilder(" SELECT COUNT(1) AS CNT FROM BUSINESS_TRANSFLOW T "); cntsqlbuilder.Append(" INNER JOIN BPSYS_STORESN N ON T.SN=N.CODE "); cntsqlbuilder.Append(" INNER JOIN BPSYS_STORE S ON S.ID=N.STOREID "); cntsqlbuilder.Append(" WHERE 1=1 "); if (queryModel.StartTime.HasValue && queryModel.FinishTime.HasValue) { cntsqlbuilder.AppendFormat(" AND T.EXCTIME BETWEEN '{0}' AND '{1}' ", queryModel.StartTime.Value.ToString(TIME_FORMAT), queryModel.FinishTime.Value.ToString(TIME_FORMAT)); } if (!String.IsNullOrEmpty(queryModel.SN)) { cntsqlbuilder.AppendFormat(" AND T.SN='{0}' ", queryModel.SN); } else { if (!String.IsNullOrEmpty(queryModel.SNName)) { cntsqlbuilder.AppendFormat(" AND N.NAME='{0}' ", queryModel.SNName); } } if (queryModel.StoreId != null && queryModel.StoreId != Guid.Empty) { cntsqlbuilder.AppendFormat(" AND S.ID='{0}' ", queryModel.StoreId); } if (!String.IsNullOrEmpty(queryModel.Source)) { cntsqlbuilder.AppendFormat(" AND T.EXCSOURCE='{0}' ", queryModel.Source); } StringBuilder sqlbulider=new StringBuilder(); if (queryModel.ReportType == 1) { //明细 sqlbulider.Append(" SELECT SN,STATUE,BANKRUNNINGNO,EXCTIME,EXCSOURCE, NETPAY, PAYABLE, HANDLING FROM BUSINESS_TRANSFLOW T"); sqlbulider.Append(" INNER JOIN BPSYS_STORESN N ON T.SN=N.CODE "); sqlbulider.Append(" INNER JOIN BPSYS_STORE S ON S.ID=N.STOREID "); sqlbulider.Append(" WHERE 1=1 "); if (!String.IsNullOrEmpty(queryModel.SN)) { sqlbulider.AppendFormat(" AND T.SN='{0}'", queryModel.SN); } else { if (!String.IsNullOrEmpty(queryModel.SNName)) { sqlbulider.AppendFormat(" AND N.NAME='{0}' ", queryModel.SNName); } } if (queryModel.StartTime.HasValue && queryModel.FinishTime.HasValue) { sqlbulider.AppendFormat(" AND EXCTIME BETWEEN '{0}' AND '{1}'", queryModel.StartTime.Value.ToString(TIME_FORMAT), queryModel.FinishTime.Value.ToString(TIME_FORMAT)); } if (queryModel.StoreId != null && queryModel.StoreId != Guid.Empty) { sqlbulider.AppendFormat(" AND S.ID='{0}' ", queryModel.StoreId); } if (!String.IsNullOrEmpty(queryModel.Source)) { sqlbulider.AppendFormat(" AND EXCSOURCE='{0}'", queryModel.Source); } } else { //汇总 if (String.IsNullOrEmpty(queryModel.SN)) { //所有SN汇总 sqlbulider.Append("SELECT SUM(NETPAY) AS NETPAY, SUM(PAYABLE) AS PAYABLE, SUM(HANDLING) AS HANDLING, EXCSOURCE, SN FROM BUSINESS_TRANSFLOW T "); sqlbulider.Append(" INNER JOIN BPSYS_STORESN N ON T.SN=N.CODE "); sqlbulider.Append(" INNER JOIN BPSYS_STORE S ON S.ID=N.STOREID "); sqlbulider.Append(" WHERE 1=1 "); if (queryModel.StartTime.HasValue && queryModel.FinishTime.HasValue) { sqlbulider.AppendFormat(" AND T.EXCTIME BETWEEN '{0}' AND '{1}'", queryModel.StartTime.Value.ToString(TIME_FORMAT), queryModel.FinishTime.Value.ToString(TIME_FORMAT)); } if (!String.IsNullOrEmpty(queryModel.SNName)) { sqlbulider.AppendFormat(" AND N.NAME='{0}' ", queryModel.SNName); } if (queryModel.StoreId != null && queryModel.StoreId != Guid.Empty) { sqlbulider.AppendFormat(" AND S.ID='{0}' ", queryModel.StoreId); } if (!String.IsNullOrEmpty(queryModel.Source)) { sqlbulider.AppendFormat(" AND T.EXCSOURCE='{0}'", queryModel.Source); } sqlbulider.Append(" GROUP BY T.SN, T.EXCSOURCE"); } else { //单独SN汇总 sqlbulider.Append("SELECT SUM(NETPAY) AS NETPAY, SUM(PAYABLE) AS PAYABLE, SUM(HANDLING) AS HANDLING, EXCSOURCE FROM BUSINESS_TRANSFLOW T "); sqlbulider.Append(" INNER JOIN BPSYS_STORESN N ON T.SN=N.CODE "); sqlbulider.Append(" INNER JOIN BPSYS_STORE S ON S.ID=N.STOREID "); sqlbulider.Append(" WHERE 1=1 "); if (queryModel.StartTime.HasValue && queryModel.FinishTime.HasValue) { sqlbulider.AppendFormat(" AND T.EXCTIME BETWEEN '{0}' AND '{1}'", queryModel.StartTime.Value.ToString(TIME_FORMAT), queryModel.FinishTime.Value.ToString(TIME_FORMAT)); } sqlbulider.AppendFormat(" AND T.SN='{0}'", queryModel.SN); if (queryModel.StoreId != null && queryModel.StoreId != Guid.Empty) { sqlbulider.AppendFormat(" AND S.ID='{0}' ", queryModel.StoreId); } if (!String.IsNullOrEmpty(queryModel.Source)) { sqlbulider.AppendFormat(" AND T.EXCSOURCE='{0}'", queryModel.Source); } sqlbulider.Append(" GROUP BY T.EXCSOURCE"); } } using (DataBaseProcess process = new DataBaseProcess()) { DataTable dtcnt = process.Query(cntsqlbuilder.ToString()); if (dtcnt != null && dtcnt.Rows.Count > 0) { Int32 i = 0; Int32.TryParse(dtcnt.Rows[0]["CNT"].ToString(), out i); result.MsgNumber = i; } DataTable dt = process.Query(sqlbulider.ToString()); result.Result = dt; } return result; }
public HandlingResult Query(SummaryReportQueryModel queryModel) { HandlingResult result = new HandlingResult(); //SELECT SUM(NETPAY)AS NETPAY, SUM(PAYABLE) AS NETPAY, SUM(HANDLING) AS NETPAY, EXCSOURCE //FROM BUSINESS_TRANSFLOW //WHERE SN = '' AND EXCTIME BETWEEN '' AND '' //GROUP BY EXCSOURCE //SELECT EXCSOURCE, NETPAY, PAYABLE, HANDLING //FROM BUSINESS_TRANSFLOW //WHERE SN = '' AND EXCTIME BETWEEN '' AND '' //SELECT SUM(NETPAY) AS NETPAY, SUM(PAYABLE) AS NETPAY, SUM(HANDLING) AS NETPAY, EXCSOURCE, SN //FROM BUSINESS_TRANSFLOW //WHERE EXCTIME BETWEEN '' AND '' //GROUP BY SN, EXCSOURCE StringBuilder cntsqlbuilder = new StringBuilder(" SELECT COUNT(1) AS CNT FROM BUSINESS_TRANSFLOW T "); cntsqlbuilder.Append(" INNER JOIN BPSYS_STORESN N ON T.SN=N.CODE "); cntsqlbuilder.Append(" INNER JOIN BPSYS_STORE S ON S.ID=N.STOREID "); cntsqlbuilder.Append(" WHERE 1=1 "); if (queryModel.StartTime.HasValue && queryModel.FinishTime.HasValue) { cntsqlbuilder.AppendFormat(" AND T.EXCTIME BETWEEN '{0}' AND '{1}' ", queryModel.StartTime.Value.ToString(TIME_FORMAT), queryModel.FinishTime.Value.ToString(TIME_FORMAT)); } if (!String.IsNullOrEmpty(queryModel.SN)) { cntsqlbuilder.AppendFormat(" AND T.SN='{0}' ", queryModel.SN); } else { if (!String.IsNullOrEmpty(queryModel.SNName)) { cntsqlbuilder.AppendFormat(" AND N.NAME='{0}' ", queryModel.SNName); } } if (queryModel.StoreId != null && queryModel.StoreId != Guid.Empty) { cntsqlbuilder.AppendFormat(" AND S.ID='{0}' ", queryModel.StoreId); } if (!String.IsNullOrEmpty(queryModel.Source)) { cntsqlbuilder.AppendFormat(" AND T.EXCSOURCE='{0}' ", queryModel.Source); } StringBuilder sqlbulider = new StringBuilder(); if (queryModel.ReportType == 1) { //明细 sqlbulider.Append(" SELECT SN,STATUE,BANKRUNNINGNO,EXCTIME,EXCSOURCE, NETPAY, PAYABLE, HANDLING FROM BUSINESS_TRANSFLOW T"); sqlbulider.Append(" INNER JOIN BPSYS_STORESN N ON T.SN=N.CODE "); sqlbulider.Append(" INNER JOIN BPSYS_STORE S ON S.ID=N.STOREID "); sqlbulider.Append(" WHERE 1=1 "); if (!String.IsNullOrEmpty(queryModel.SN)) { sqlbulider.AppendFormat(" AND T.SN='{0}'", queryModel.SN); } else { if (!String.IsNullOrEmpty(queryModel.SNName)) { sqlbulider.AppendFormat(" AND N.NAME='{0}' ", queryModel.SNName); } } if (queryModel.StartTime.HasValue && queryModel.FinishTime.HasValue) { sqlbulider.AppendFormat(" AND EXCTIME BETWEEN '{0}' AND '{1}'", queryModel.StartTime.Value.ToString(TIME_FORMAT), queryModel.FinishTime.Value.ToString(TIME_FORMAT)); } if (queryModel.StoreId != null && queryModel.StoreId != Guid.Empty) { sqlbulider.AppendFormat(" AND S.ID='{0}' ", queryModel.StoreId); } if (!String.IsNullOrEmpty(queryModel.Source)) { sqlbulider.AppendFormat(" AND EXCSOURCE='{0}'", queryModel.Source); } } else { //汇总 if (String.IsNullOrEmpty(queryModel.SN)) { //所有SN汇总 sqlbulider.Append("SELECT SUM(NETPAY) AS NETPAY, SUM(PAYABLE) AS PAYABLE, SUM(HANDLING) AS HANDLING, EXCSOURCE, SN FROM BUSINESS_TRANSFLOW T "); sqlbulider.Append(" INNER JOIN BPSYS_STORESN N ON T.SN=N.CODE "); sqlbulider.Append(" INNER JOIN BPSYS_STORE S ON S.ID=N.STOREID "); sqlbulider.Append(" WHERE 1=1 "); if (queryModel.StartTime.HasValue && queryModel.FinishTime.HasValue) { sqlbulider.AppendFormat(" AND T.EXCTIME BETWEEN '{0}' AND '{1}'", queryModel.StartTime.Value.ToString(TIME_FORMAT), queryModel.FinishTime.Value.ToString(TIME_FORMAT)); } if (!String.IsNullOrEmpty(queryModel.SNName)) { sqlbulider.AppendFormat(" AND N.NAME='{0}' ", queryModel.SNName); } if (queryModel.StoreId != null && queryModel.StoreId != Guid.Empty) { sqlbulider.AppendFormat(" AND S.ID='{0}' ", queryModel.StoreId); } if (!String.IsNullOrEmpty(queryModel.Source)) { sqlbulider.AppendFormat(" AND T.EXCSOURCE='{0}'", queryModel.Source); } sqlbulider.Append(" GROUP BY T.SN, T.EXCSOURCE"); } else { //单独SN汇总 sqlbulider.Append("SELECT SUM(NETPAY) AS NETPAY, SUM(PAYABLE) AS PAYABLE, SUM(HANDLING) AS HANDLING, EXCSOURCE FROM BUSINESS_TRANSFLOW T "); sqlbulider.Append(" INNER JOIN BPSYS_STORESN N ON T.SN=N.CODE "); sqlbulider.Append(" INNER JOIN BPSYS_STORE S ON S.ID=N.STOREID "); sqlbulider.Append(" WHERE 1=1 "); if (queryModel.StartTime.HasValue && queryModel.FinishTime.HasValue) { sqlbulider.AppendFormat(" AND T.EXCTIME BETWEEN '{0}' AND '{1}'", queryModel.StartTime.Value.ToString(TIME_FORMAT), queryModel.FinishTime.Value.ToString(TIME_FORMAT)); } sqlbulider.AppendFormat(" AND T.SN='{0}'", queryModel.SN); if (queryModel.StoreId != null && queryModel.StoreId != Guid.Empty) { sqlbulider.AppendFormat(" AND S.ID='{0}' ", queryModel.StoreId); } if (!String.IsNullOrEmpty(queryModel.Source)) { sqlbulider.AppendFormat(" AND T.EXCSOURCE='{0}'", queryModel.Source); } sqlbulider.Append(" GROUP BY T.EXCSOURCE"); } } using (DataBaseProcess process = new DataBaseProcess()) { DataTable dtcnt = process.Query(cntsqlbuilder.ToString()); if (dtcnt != null && dtcnt.Rows.Count > 0) { Int32 i = 0; Int32.TryParse(dtcnt.Rows[0]["CNT"].ToString(), out i); result.MsgNumber = i; } DataTable dt = process.Query(sqlbulider.ToString()); result.Result = dt; } return(result); }