/// <summary>
 /// 開立發票列表
 /// </summary>
 /// <param name="imrq"></param>
 /// <param name="totalCount"></param>
 /// <returns></returns>
 public DataTable GetInvoiceList(InvoiceMasterRecordQuery imrq, out int totalCount)
 {
     try
     {
         return _imrDao.GetInvoiceList(imrq, out totalCount);
     }
     catch (Exception ex)
     {
         throw new Exception("InvoiceMasterRecordMgr-->GetInvoiceList" + ex.Message, ex);
     }
 }
 public DataTable ExportExlF(InvoiceMasterRecordQuery imrq)
 {
     try
     {
         return _imrDao.ExportExlF(imrq);
     }
     catch (Exception ex)
     {
         throw new Exception("InvoiceMasterRecordMgr-->ExportExlF" + ex.Message, ex);
     }
 }
        public DataTable ExportExlF(InvoiceMasterRecordQuery imrq)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"SELECT DISTINCT order_id ,om.order_name,om.order_amount,om.order_status,om.order_payment,om.order_createdate,");
            sql.AppendFormat(@"om.order_freight_normal,om.order_freight_low,om.bonus_type,om.deduct_happygo,om.deduct_happygo_convert,");
            sql.AppendFormat(@" ct.offsetamt FROM order_master om  LEFT JOIN invoice_master_record  imr  ");
            sql.AppendFormat(@" USING (order_id) LEFT JOIN order_payment_ct ct ON ct.lidm = om.order_id WHERE 1=1 ");
            sql.Append(imrq.sqlwhere);
            sql.AppendFormat(@" ORDER BY imr.invoice_id DESC");
            try
            {
                return _access.getDataTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("InvoiceMasterRecordDao-->ExportExlF" + ex.Message + sql.ToString(), ex);
            }

        }
        public DataTable ExportTXT(InvoiceMasterRecordQuery imrq)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(@" SELECT invoice_number,invoice_date,company_invoice,tax_type,tax_amount,free_tax,CAST(invoice_status AS CHAR) AS invoice_status, ");
            sql.AppendLine(@" total_amount,invoice_attribute,FROM_UNIXTIME(status_createdate) AS status_createdate");
            sql.AppendLine(@"  FROM invoice_master_record WHERE 1=1");
            sql.Append(imrq.sqlwhere);
            if (imrq.order_id != 0)
            {
                sql.AppendFormat(@" AND order_id='{0}' ",imrq.order_id);
            }
            try
            {
                return _access.getDataTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("InvoiceMasterRecordDao-->ExportTXT" + ex.Message + sql.ToString(), ex);
            }

        }
 public DataTable GetInvoiceList(InvoiceMasterRecordQuery imrq, out int totalCount)
 {
     StringBuilder sql = new StringBuilder();
     StringBuilder sqlfield = new StringBuilder();
     StringBuilder sqlfrom = new StringBuilder();
     StringBuilder sqlwhere = new StringBuilder();
     sqlfield.AppendLine(@"SELECT imr.invoice_id,imr.order_id,FROM_UNIXTIME(om.order_date_pay) AS order_date_pay,");
     sqlfield.AppendLine(@"para.parameterName AS order_payment,ops.sinopac_id,CAST(imr.invoice_status AS CHAR) AS invoice_status,imr.invoice_number, ");
     sqlfield.AppendLine(@"FROM_UNIXTIME(imr.invoice_date) AS invoice_date,imr.free_tax,imr.tax_amount,imr.sales_amount,");
     sqlfield.AppendLine(@"CAST(imr.buyer_type AS CHAR) AS buyer_type,imr.buyer_name,imr.company_invoice,imr.company_title,");
     sqlfield.AppendLine(@"CAST(imr.order_zip AS CHAR) AS order_zip,imr.order_address,imr.order_address AS aorder_address,");
     sqlfield.AppendLine(@"FROM_UNIXTIME(imr.print_post_createdate) AS print_post_createdate,FROM_UNIXTIME(imr.status_createdate) AS status_createdate,");
     sqlfield.AppendLine(@"imr.invoice_note,imr.invoice_attribute,imr.total_amount,om.order_amount,om.money_cancel,om.money_return");
     sqlfield.AppendLine(@",SUBSTRING(imr.invoice_number,2,8) AS invoice_number2 ");
     sqlfrom.AppendLine(@" FROM invoice_master_record  imr  ");
     sqlfrom.AppendLine(@" LEFT JOIN order_master  om  ON imr.order_id = om.order_id");
     sqlfrom.AppendLine(@" LEFT JOIN order_payment_sinopac ops ON om.order_id = ops.order_id ");
     sqlfrom.AppendLine(@" LEFT JOIN  (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='payment') para ON para.parameterCode=om.order_payment");
     sqlwhere.AppendLine(@" WHERE 1=1 ");
     sqlwhere.AppendLine(imrq.sqlwhere);
     sql.Append(sqlfield.ToString() + sqlfrom.ToString() + sqlwhere.ToString());
     sql.AppendLine(@" ORDER BY imr.invoice_id DESC ");
     totalCount = 0;
     if (imrq.IsPage)
     {
         sql.AppendFormat(" limit {0},{1}", imrq.Start, imrq.Limit);
         System.Data.DataTable _dt = _access.getDataTable(" SELECT imr.invoice_id " + sqlfrom.ToString() + sqlwhere.ToString());
         totalCount = _dt.Rows.Count;
     }
     try
     {
         return _access.getDataTable(sql.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("InvoiceMasterRecordDao-->GetInvoiceList" + ex.Message + sql.ToString(), ex);
     }
 }
        /// <summary>
        /// 發票記錄
        /// </summary>
        /// <returns></returns>
        public HttpResponseBase GetInvoiceMasterList()
        {
            string json = string.Empty;

            InvoiceMasterRecordQuery query = new InvoiceMasterRecordQuery();
            query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
            query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
            if (!string.IsNullOrEmpty(Request.Params["Order_Id"]))
            {
                query.order_id = uint.Parse(Request.Params["Order_Id"]);
            }
            try
            {
                List<InvoiceMasterRecordQuery> store = new List<InvoiceMasterRecordQuery>();
                _tbshow = new TabShowMgr(mySqlConnectionString);
                int totalCount = 0;
                store = _tbshow.GetInvoiceMasterRecord(query, out  totalCount);
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式     
                timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
                json = "{success:true,data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:true,totalCount:0,data:[]}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;

        }
Beispiel #7
0
        public List<InvoiceMasterRecordQuery> GetInvoiceMasterRecord(InvoiceMasterRecordQuery store, out int totalCount)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                sb.Append(@" SELECT invoice_id,invoice_number,tax_type,free_tax,tax_amount,total_amount,invoice_status,invoice_attribute,(FROM_UNIXTIME(invoice_date)) as open_date ");
                sb.AppendFormat("   FROM	invoice_master_record WHERE	order_id ='{0}' ", store.order_id);
                totalCount = 0;
                if (store.IsPage)
                {
                    System.Data.DataTable _dt = _access.getDataTable(sb.ToString());
                    if (_dt != null && _dt.Rows.Count > 0)
                    {
                        totalCount = _dt.Rows.Count;
                    }
                    sb.AppendFormat(" limit {0},{1}", store.Start, store.Limit);
                }
                return _access.getDataTableForObj<InvoiceMasterRecordQuery>(sb.ToString());
            }
            catch (Exception ex)
            {

                throw new Exception("TabShowDao-->GetInvoiceMasterRecord " + ex.Message + sb.ToString(), ex);
            }

        }
Beispiel #8
0
 public List<InvoiceMasterRecordQuery> GetInvoiceMasterRecord(InvoiceMasterRecordQuery store, out int totalCount)
 {
     try
     {
         return _tabshowdao.GetInvoiceMasterRecord(store, out totalCount);
     }
     catch (Exception ex)
     {
         throw new Exception("TabShowMgr-->GetInvoiceMasterRecord-->" + ex.Message, ex);
     }
 }