public string UpdateOrderMaster(OrderMasterQuery query, OrderShowMasterQuery osmQuery)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("update order_master set delivery_store='{0}' ", query.Delivery_Store);
if (query.payment == "T_CAT")
{
sql.AppendFormat(" ,order_freight_normal='{0}',order_freight_low='{1}',order_payment='{2}',order_date_pay='{3}' ", query.Order_Freight_Normal, query.Order_Freight_Low, query.Order_Payment, CommonFunction.GetPHPTime());
if (query.t_cat_amount != 0)
{
sql.AppendFormat(",order_amount='{0}' ", query.Order_Amount);
}
}
else if (osmQuery.order_status == 0)
{
sql.AppendFormat(" ,order_freight_normal='{0}',order_freight_low='{1}',order_amount='{2}',order_payment='{3}',order_date_pay='{4}' ", query.Order_Freight_Normal, query.Order_Freight_Low, query.Order_Amount, query.Order_Payment, CommonFunction.GetPHPTime());
}
sql.AppendFormat(" where order_id='{0}';", query.Order_Id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->UpDeliveryStore-->" + sql.ToString() + ex.Message, ex);
}
}
public HttpResponseBase ModifyDeliveryData()
{
string json = string.Empty;
OrderMasterQuery om = new OrderMasterQuery();
DeliverMasterQuery dm = new DeliverMasterQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["order_id"]))
{
om.Order_Id = Convert.ToUInt32(Request.Params["order_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["user_name"]))
{
om.delivery_name = Request.Params["user_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["user_gender"]))
{
om.user_gender = Convert.ToUInt32(Request.Params["user_gender"]);//delivery_gender
}
if (!string.IsNullOrEmpty(Request.Params["user_mobile"]))
{
om.Delivery_Mobile = Request.Params["user_mobile"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["user_phone"]))
{
om.Delivery_Phone = Request.Params["user_phone"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["user_zip"]))
{
om.Delivery_Zip = Convert.ToUInt32(Request.Params["user_zip"]);
}
if (!string.IsNullOrEmpty(Request.Params["user_address"]))
{
om.Delivery_Address = Request.Params["user_address"].ToString();
}
om.user_id = Convert.ToUInt32((Session["caller"] as Caller).user_id);
om.user_name = (Session["caller"] as Caller).user_username;
System.Net.IPAddress[] addlist = Dns.GetHostByName(Dns.GetHostName()).AddressList;
om.Order_Ipfrom = addlist[0].ToString();
_orderMasterMgr = new OrderMasterMgr(mySqlConnectionString);
json = _orderMasterMgr.ModifyDeliveryData(om);
}
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:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public bool Start(string schedule_code)
{
bool result = false;
try
{
if (string.IsNullOrEmpty(schedule_code))
{
return result;
}
string GroupCode = string.Empty;
string MailTitle = string.Empty;
StringBuilder strbody = new StringBuilder();
MailModel mailModel = new MailModel();
mailModel.MysqlConnectionString = mySqlConnectionString;
//獲取該排程參數
List<ScheduleConfigQuery> store_config = new List<ScheduleConfigQuery>();
ScheduleConfigQuery query_config = new ScheduleConfigQuery();
query_config.schedule_code = schedule_code;
_secheduleServiceMgr = new ScheduleServiceMgr(mySqlConnectionString);
store_config = _secheduleServiceMgr.GetScheduleConfig(query_config);
#region FTP參數賦值
foreach (ScheduleConfigQuery item in store_config)
{
if (item.parameterCode.Equals("MailFromAddress"))
{
mailModel.MailFromAddress = item.value;
}
else if (item.parameterCode.Equals("MailHost"))
{
mailModel.MailHost = item.value;
}
else if (item.parameterCode.Equals("MailPort"))
{
mailModel.MailPort = item.value;
}
else if (item.parameterCode.Equals("MailFromUser"))
{
mailModel.MailFromUser = item.value;
}
else if (item.parameterCode.Equals("EmailPassWord"))
{
mailModel.MailFormPwd = item.value;
}
else if (item.parameterCode.Equals("GroupCode"))
{
GroupCode = item.value;
}
else if (item.parameterCode.Equals("MailTitle"))
{
MailTitle = item.value;
}
else if (item.parameterCode.Equals("MailTitle"))
{
MailTitle = item.value;
}
}
#endregion
MailHelper mail = new MailHelper(mailModel);
OrderMasterQuery query = new OrderMasterQuery();
query.order_date_pay_endTime = DateTime.Now;
query.order_date_pay_startTime = query.order_date_pay_endTime.AddHours(-1);
StringBuilder sbMailBody = new StringBuilder();
try
{
bool isSHow = false;
string html =GetBigAmountMailBody(query,out isSHow);
if (isSHow)
{
sbMailBody.AppendLine(html);
}
}
catch (Exception ex)
{
throw new Exception("CheckOrderAmountMgr-->GetBigAmountMailBody-->" + "大金額訂單排程:" + ex.Message);
}
try
{
bool isSHow = false;
string html = GetBigOrderNumbersMailBody(query, out isSHow);
if (isSHow)
{
sbMailBody.AppendLine(html);
}
}
catch (Exception ex)
{
throw new Exception("CheckOrderAmountMgr-->GetBigOrderNumbersMailBody-->"+"一小時多次付款客戶異常訂單付款通知:"+ ex.Message);
}
try
{
bool isSHow = false;
string html = FirstBuyEmail(query, out isSHow);
if (isSHow)
{
sbMailBody.AppendLine(html);
}
}
catch (Exception ex)
{
throw new Exception("CheckOrderAmountMgr-->OtherTWPay-->" + "首購超過5000訂單付款檢查:" + ex.Message);
}
try
{
bool isSHow = false;
string html = OtherTWPay(query, out isSHow);
if (isSHow)
{
sbMailBody.AppendLine(html);
}
}
catch (Exception ex)
{
throw new Exception("CheckOrderAmountMgr-->OtherTWPay-->" + "異地信用卡付款付款訂單通知:" + ex.Message);
}
if (!String.IsNullOrEmpty(sbMailBody.ToString()))
{
mail.SendToGroup(GroupCode, MailTitle, sbMailBody.ToString(), false, true);//發送郵件給群組
}
result= true;
}
catch (Exception ex)
{
throw new Exception("CheckOrderAmount-->Start-->" + ex.Message);
}
return result;
}
public void OrderSerchExport()
{
List<OrderMasterQuery> stores = new List<OrderMasterQuery>();
OrderMasterQuery query = new OrderMasterQuery();
int id; uint uid; DateTime dtime;
#region 查詢條件
if (int.TryParse(Request.Params["Vip_User_Group"].ToString(), out id))//會員群組
{
query.group_id = id;
}
if (int.TryParse(Request.Params["invoice"].ToString(), out id))//過濾已開發票
{
query.invoice = id;
}
if (uint.TryParse(Request.Params["channel"].ToString(), out uid))//賣場
{
query.Channel = uid;
}
if (int.TryParse(Request.Params["order_pay"].ToString(), out id))//付款狀態
{
query.pay_status = id;
}
if (!string.IsNullOrEmpty(Request.Params["selecttype"]))//查詢種類，訂單編號，訂購姓名
{
query.selecttype = Request.Params["selecttype"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["searchcon"]))//查詢內容
{
query.searchcon = Request.Params["searchcon"].ToString();
}
if (int.TryParse(Request.Params["timeone"].ToString(), out id))//時間類型
{
query.dateType = id;
}
if (DateTime.TryParse(Request.Params["dateOne"].ToString(), out dtime))//開始時間
{
query.datestart = dtime;
}
if (DateTime.TryParse(Request.Params["dateTwo"].ToString(), out dtime))
{
query.dateend = dtime;
}
if (int.TryParse(Request.Params["page_status"].ToString(), out id))//付款單狀態
{
query.orderStatus = Request.Params["page_status"];
}
if (uint.TryParse(Request.Params["order_payment"], out uid))//付款方式，AT
{
query.Order_Payment = uid;
}
#endregion
string json = string.Empty;
DataTable dtHZ = new DataTable();
DataTable dt_payment = new DataTable();
DataTable dt_status = new DataTable();
dt_payment = GetTP("payment");
dt_status = GetTP("order_status");
string newExcelName = string.Empty;
dtHZ.Columns.Add("付款單號", typeof(String));
dtHZ.Columns.Add("訂單人", typeof(String));
dtHZ.Columns.Add("收貨人", typeof(String));
dtHZ.Columns.Add("訂單應收金額", typeof(String));
dtHZ.Columns.Add("付款方式", typeof(String));
dtHZ.Columns.Add("付款單狀態", typeof(String));
dtHZ.Columns.Add("付款單成立日期", typeof(String));
dtHZ.Columns.Add("購物金抵用金額", typeof(String));
dtHZ.Columns.Add("抵用券抵用金額", typeof(String));
dtHZ.Columns.Add("HG抵用金額", typeof(String));
dtHZ.Columns.Add("常溫運費", typeof(String));
dtHZ.Columns.Add("低溫運費", typeof(String));
dtHZ.Columns.Add("付款時間", typeof(String));
dtHZ.Columns.Add("銀行紅利折抵", typeof(String));
dtHZ.Columns.Add("取消金額", typeof(String));
dtHZ.Columns.Add("退貨金額", typeof(String));
dtHZ.Columns.Add("來源群組", typeof(String));
dtHZ.Columns.Add("來源", typeof(String));
dtHZ.Columns.Add("UTM_CODE", typeof(String));
dtHZ.Columns.Add("UTM_NAME", typeof(String));
dtHZ.Columns.Add("訂購人編號", typeof(String));
//dtHZ.Columns.Add("訂購人手機", typeof(String));
dtHZ.Columns.Add("虛擬帳號", typeof(String));
dtHZ.Columns.Add("賣場", typeof(String));
dtHZ.Columns.Add("外站訂單編號", typeof(String));
dtHZ.Columns.Add("管理員備註", typeof(String));
try
{
int totalCount = 0;
_ordermaster = new OrderMasterMgr(mySqlConnectionString);
stores = _ordermaster.Export(query, "", out totalCount);
foreach (var item in stores)
{
DataRow dr = dtHZ.NewRow();
dr[0] = item.Order_Id;
dr[1] = item.Order_Name;
dr[2] = item.Delivery_Name;
dr[3] = item.Order_Amount;
dr[4] = item.Order_Payment;
dr[5] = item.Order_Status;
if (item.Order_Payment > 0)
{//帶出付款方式的參數
DataRow[] drs = dt_payment.Select("ParameterCode = '" + item.Order_Payment.ToString() + "'");
DataTable _newdt = dt_payment.Clone();
foreach (DataRow i in drs)
{
_newdt.Rows.Add(i.ItemArray);
}
dr[4] = _newdt.Rows[0]["ParameterName"].ToString();
}
if (item.Order_Status >= 0)
{//帶出付款方式的參數
DataRow[] drs = dt_status.Select("ParameterCode = '" + item.Order_Status.ToString() + "'");
DataTable _newdt = dt_status.Clone();
foreach (DataRow i in drs)
{
_newdt.Rows.Add(i.ItemArray);
}
dr[5] = _newdt.Rows[0]["remark"].ToString();
}
dr[6] = item.Order_Createdate == 0 ? "" : CommonFunction.GetNetTime(item.Order_Createdate).ToString();
dr[7] = item.Deduct_Bonus;
dr[8] = item.Deduct_Welfare;
dr[9] = item.Deduct_Happygo == 0 ? 0 : Math.Round(item.Deduct_Happygo*item.deduct_happygo_convert);
dr[10] = item.Order_Freight_Normal;
dr[11] = item.Order_Freight_Low;
dr[12] = item.Order_Date_Pay == 0 ? "未付款" : CommonFunction.GetNetTime(item.Order_Date_Pay).ToString();
dr[13] = item.Deduct_Card_Bonus;
dr[14] = item.Money_Cancel;
dr[15] = item.Money_Return;
dr[16] = item.group_name;
dr[17] = item.redirect_name;
dr[18] = item.utm_id;
dr[19] = item.utm_source;
dr[20] = item.User_Id;
//dr[21] = " "+ item.Order_Mobile;
dr[21] = " " + item.hncb_id;
dr[22] = item.channel_name_full;
dr[23] = item.Channel_Order_Id;
dr[24] = item.Note_Admin;
dtHZ.Rows.Add(dr);
}
if (stores.Count > 0)
{
string fileName = "訂單查詢匯出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "");
Response.AddHeader("Content-Disposition", "attach-ment;filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
else
{
Response.Write("匯出數據不存在");
}
}
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 = "false, ";
}
}
public HttpResponseBase GetData()
{
uint orderId =0;
if(uint.TryParse(Request.Params["order_id"],out orderId))
{
orderId = orderId;
}
OrderMasterQuery omQuery=new OrderMasterQuery ();
OrderShowMasterQuery query = new OrderShowMasterQuery();
string json = string.Empty;
OrderShowMasterQuery store = new OrderShowMasterQuery();
_orderMasterMgr = new OrderMasterMgr(mySqlConnectionString);
zMgr = new ZipMgr(mySqlConnectionString);
try
{
store = _orderMasterMgr.GetData(orderId);
uint user_id =(uint)(Session["caller"] as Caller).user_id;
if (_orderMasterMgr.VerifySession(user_id) > 0)
{
store.is_manage_user = true;
}
else
{
store.is_manage_user = false;
}
if (!string.IsNullOrEmpty(Request.Params["isSecret"]))
{
if (Request.Params["isSecret"] == "false")
{
store.isSecret = false;
}
else
{
store.isSecret = true;
}
}
omQuery.Order_Id=orderId;
store.is_send_product = _orderMasterMgr.IsSendProduct(omQuery);//true可以變更 false不可變更。
store.is_vendor_deliver = _orderMasterMgr.IsVendorDeliver(orderId);
store.OrderDatePay = CommonFunction.GetNetTime(store.order_date_pay);
if (!string.IsNullOrEmpty(store.money_collect_date.ToString()) && store.money_collect_date != 0)
{
store.MoneyCollectDate = CommonFunction.GetNetTime(store.money_collect_date);
}
store.NoteOrderModifyTime = CommonFunction.GetNetTime(store.note_order_modify_time);
store.OrderCreateDate = CommonFunction.GetNetTime(store.order_createdate);
store.OrderDateClose = CommonFunction.GetNetTime(store.order_date_close);
double value = Convert.ToDouble(store.deduct_happygo * store.deduct_happygo_convert);
store.Hg_Nt = Math.Round(value).ToString();
//地址直接讀取
store.order_address = zMgr.Getaddress(int.Parse(store.order_zip.ToString())) + store.order_address;
store.delivery_address = zMgr.Getaddress(int.Parse(store.delivery_zip.ToString())) + store.delivery_address;
if (store.isSecret)
{
#region 添加資安的字段
if (store.order_name.Length > 1)
{
store.order_name = store.order_name.ToString().Substring(0, 1) + "**";
}
else
{
store.order_name = store.order_name.ToString() + "**";
}
//if (store.order_mobile.Length > 1)
//{
// store.order_mobile = store.order_mobile.ToString().Substring(0, 1) + "****";
//}
//else
//{
// store.order_mobile = store.order_mobile.ToString() + "**";
//}
if (!string.IsNullOrEmpty(store.order_mobile))
{
if (!CommonFunction.isMobile(store.order_mobile))
{
if (store.order_mobile.ToString().Length == 48)
{
store.order_mobile = EncryptComputer.EncryptDecryptTextByApi(store.order_mobile, false);
if (!CommonFunction.isMobile(store.order_mobile))
{
//異常記錄
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("表名:{0},訂單編號:{1},行動電話:{2},Message:{3}", "order_master", store.order_id, store.order_mobile, "訂購人行動電話解密后不滿足正則表達式");
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
else
{
//異常記錄
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("表名:{0},訂單編號:{1},行動電話:{2},Message:{3}", "order_master", store.order_id, store.order_mobile, "訂購人行動電話錯誤");
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
if (store.order_mobile.ToString().Length > 3)
{
store.order_mobile = store.order_mobile.Substring(0, 3) + "***";
}
else
{
store.order_mobile = store.order_mobile + "***";
}
}
if (store.order_address.Length > 3)
{
store.order_address = store.order_address.ToString().Substring(0, 3) + "****";
}
else
{
store.order_address = store.order_address.ToString() + "**";
}
if (store.delivery_name.Length > 1)
{
store.delivery_name = store.delivery_name.ToString().Substring(0, 1) + "**";
}
else
{
store.delivery_name = store.delivery_name.ToString() + "**";
}
//if (store.delivery_mobile.Length > 1)
//{
// store.delivery_mobile = store.delivery_mobile.ToString().Substring(0, 1) + "****";
//}
//else
//{
// store.delivery_mobile = store.delivery_mobile.ToString() + "**";
//}
if (!string.IsNullOrEmpty(store.delivery_mobile))
{
if (!CommonFunction.isMobile(store.delivery_mobile))
{
if (store.delivery_mobile.ToString().Length == 48)
{
store.delivery_mobile = EncryptComputer.EncryptDecryptTextByApi(store.delivery_mobile, false);
if (!CommonFunction.isMobile(store.delivery_mobile))
{
//異常記錄
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("表名:{0},訂單編號:{1},行動電話:{2},Message:{3}", "order_master", store.order_id, store.delivery_mobile, "收貨人行動電話解密后不滿足正則表達式");
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
else
{
//異常記錄
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("表名:{0},訂單編號:{1},行動電話:{2},Message:{3}", "order_master", store.order_id, store.delivery_mobile, "收貨人行動電話錯誤");
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
if (store.delivery_mobile.ToString().Length > 3)
{
store.delivery_mobile = store.delivery_mobile.Substring(0, 3) + "***";
}
else
{
store.delivery_mobile = store.delivery_mobile + "***";
}
}
if (store.delivery_address.Length > 3)
{
store.delivery_address = store.delivery_address.ToString().Substring(0, 3) + "****";
}
else
{
store.delivery_address = store.delivery_address.ToString() + "**";
}
#endregion
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
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:false,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 商品點擊信息列表
/// </summary>
/// <returns></returns>
public HttpResponseBase BrowseDataList()
{
string json = string.Empty;
DataTable dtBrowseData = new DataTable();
int totalCount = 0;
BrowseDataQuery query = new BrowseDataQuery();
string type=Request.Params["type"];
string searchContent = Request.Params["searchContent"];
if (!string.IsNullOrEmpty(type))
{
query.type = Convert.ToInt32(type);
}
if (!string.IsNullOrEmpty(searchContent))
{
query.SearchCondition = searchContent;
}
if (!string.IsNullOrEmpty(Request.Params["true"]))
{
if (Request.Params["true"] == "true")
{
query.isSecret = true;
}
else
{
query.isSecret = false;
}
}
if (!string.IsNullOrEmpty(Request.Params["searchType"]))
{
query.SearchType = Convert.ToInt32(Request.Params["searchType"]);
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
_IBrowseDataMgr = new BrowseDataMgr(mySqlConnectionString);
_IProductItemMgr=new ProductItemMgr(mySqlConnectionString);
_IOrderMasterMgr = new OrderMasterMgr(mySqlConnectionString);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
try
{
dtBrowseData = _IBrowseDataMgr.GetBrowseDataList(query, out totalCount);
if (dtBrowseData.Rows.Count>0)
{
dtBrowseData.Columns.Add("buyCount");
for (int i = 0; i < dtBrowseData.Rows.Count; i++)
{
string user_id = dtBrowseData.Rows[i]["user_id"].ToString();
string product_id = dtBrowseData.Rows[i]["product_id"].ToString();
string item_id = string.Empty;
if (query.isSecret)
{
dtBrowseData.Rows[i]["user_name"] = dtBrowseData.Rows[i]["user_name"].ToString().Substring(0, 1) + "**";
}
List<ProductItem> listProductItem = _IProductItemMgr.GetProductItemByID(Convert.ToInt32(product_id));
if (listProductItem.Count > 0)
{
item_id = listProductItem[0].Item_Id.ToString();
}
OrderMasterQuery orderMasterQuery = new OrderMasterQuery
{
User_Id = Convert.ToUInt32(dtBrowseData.Rows[i]["user_id"]),
Item_Id = Convert.ToInt32(item_id)
};
dtBrowseData.Rows[i]["buyCount"] = _IOrderMasterMgr.GetBuyCount(orderMasterQuery);
}
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dtBrowseData, Formatting.Indented, timeConverter) + "}";
}
else
{
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dtBrowseData, Formatting.Indented, timeConverter) + "}";
//json = "{success:false,msg:0}";
}
}
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:false,msg:0}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 訂單內容
/// </summary>
/// <returns></returns>
public ActionResult OrderDetialList()
{
//vendorServerPath
ViewBag.VendorServerPath = vendorServerPath;
ViewBag.OrderId = Request.QueryString["Order_Id"] ?? "";//獲取付款單號
#region 控制List頁面字段顯示與否
OrderMasterQuery query = new OrderMasterQuery();
_orderMasterMgr = new OrderMasterMgr(mySqlConnectionString);
query.Order_Id = Convert.ToUInt32(Request.QueryString["Order_Id"] ?? "");
string result = _orderMasterMgr.VerifyData(query.Order_Id);
string[] preValue = result.Split(';');
ViewBag.site = preValue[0];
ViewBag.sinopa = preValue[1];
ViewBag.channel = preValue[2];
ViewBag.hncb = preValue[3];
ViewBag.companyWrite = preValue[4].ToString();
ViewBag.importTime = CommonFunction.GetNetTime(CommonFunction.GetPHPTime(preValue[5].ToString())).ToString("yyyy-MM-dd HH:mm:ss");
ViewBag.channelOderId = preValue[6];
ViewBag.retrieve_mode = preValue[7];
ViewBag.delivery_same = preValue[8];
ViewBag.delivery_name = preValue[9];
ViewBag.c_delivery_gender = preValue[10];
#endregion
return View();
}
public DataTable CheckDeliveryStatus(OrderMasterQuery om)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select deliver_id,delivery_status from deliver_master where order_id='{0}';", om.Order_Id);
return _dbAccess.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->CheckDeliveryStatus-->" + sql.ToString() + ex.Message, ex);
}
}
public string UpdateDM(OrderMasterQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("update deliver_master set delivery_name='{0}',delivery_mobile='{1}',delivery_phone='{2}',delivery_zip='{3}',delivery_address='{4}' where order_id='{5}';", query.delivery_name, query.Delivery_Mobile, query.Delivery_Phone, query.Delivery_Zip, query.Delivery_Address, query.Order_Id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->UpdataDM-->" + sql.ToString() + ex.Message, ex);
}
}
public DataTable IsFirstTime(OrderMasterQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" select user_id from users where user_id='{0}' and first_time=0; ", query.user_id);
return _dbAccess.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->IsFirstTime-->" + sql.ToString() + ex.Message, ex);
}
}
public string UpFirstTime(OrderMasterQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("update order_master om,users u set om.priority=1,u.first_time='{0}' where om.order_id='{1}' and om.user_id=u.user_id;", CommonFunction.GetPHPTime(), query.Order_Id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->UpFirstTime-->" + sql.ToString() + ex.Message, ex);
}
}
public string ModifyOrderStatus(OrderMasterQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("UPDATE order_master om,order_slave os,order_detail od SET om.order_status='2',os.slave_status='2',od.detail_status='2',om.order_date_pay='{0}',om.order_date_cancel=0,om.money_cancel=0,om.export_flag=1 where om.order_id='{1}' AND om.order_id=os.order_id AND os.slave_id=od.slave_id;", CommonFunction.GetPHPTime(), query.Order_Id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->ModifyOrderStatus-->" + sql.ToString() + ex.Message, ex);
}
}
public DataTable GetInfo(OrderMasterQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select om.order_id,os.slave_id,od.detail_id from order_master om LEFT JOIN order_slave os on om.order_id=os.order_id LEFT JOIN order_detail od on os.slave_id=od.slave_id where om.order_id='{0}';", query.Order_Id);
return _dbAccess.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->GetInfo-->" + sql.ToString() + ex.Message, ex);
}
}
public string UpOrderMaster(OrderMasterQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.mdd)
{
sql.AppendFormat("update order_master set delivery_name='{0}',delivery_gender='{1}',delivery_mobile='{2}',delivery_phone='{3}',delivery_zip='{4}',delivery_address='{5}',delivery_same='0',note_order='{6}' where order_id='{7}';", query.delivery_name, query.user_gender, query.Delivery_Mobile, query.Delivery_Phone, query.Delivery_Zip, query.Delivery_Address, query.note_order, query.Order_Id);
}
else
{
sql.AppendFormat("update order_master set order_status='2',order_date_pay='{0}',order_date_cancel='{1}',money_cancel=0,export_flag=1 where order_id='{1}';", CommonFunction.GetPHPTime(), query.Order_Id);
}
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->UpOrderMaster-->" + sql.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 根據條件查詢出貨列表
/// </summary>
/// <param name="query">查詢條件</param>
/// <param name="totalCount">返回數據總條數</param>
/// <returns>出貨列表信息</returns>
public List<OrderMasterQuery> GetShipmentList(OrderMasterQuery query, out int totalCount)
{
try
{
query.Replace4MySQL();
string strSql = string.Empty;
StringBuilder sbCols = new StringBuilder("SELECT om.order_id as OrderId,FROM_UNIXTIME(om.order_date_pay) as OrderDatePay,FROM_UNIXTIME(om.money_collect_date) as MoneyCollectDate,om.order_status as Order_Status,om.note_order,dm.deliver_id,dm.freight_set,dm.type,v.vendor_id,v.vendor_name_simple,od.product_name,od.product_spec_name,od.buy_num,om.order_name,dm.delivery_name,dm.delivery_status,od.detail_status,od.combined_mode,od.parent_name,od.item_mode ");//om.money_collect_date,
StringBuilder sbTbls = new StringBuilder();
sbTbls.Append(" FROM order_master AS om ");
sbTbls.Append(" LEFT JOIN deliver_master AS dm ON (om.order_id = dm.order_id) ");
sbTbls.Append(" LEFT JOIN deliver_detail AS dd ON (dm.deliver_id = dd.deliver_id) ");
sbTbls.Append(" LEFT JOIN order_detail AS od ON (od.detail_id = dd.detail_id) ");
sbTbls.Append(" LEFT JOIN vendor AS v ON (v.vendor_id = od.item_vendor_id) ");
StringBuilder sbCondition = new StringBuilder(" WHERE 1=1 ");
if (!string.IsNullOrEmpty(query.order_date_pay_startTime.ToString()))
{
sbCondition.AppendFormat(" AND order_date_pay >= {0} ", CommonFunction.GetPHPTime(query.order_date_pay_startTime.ToString()));
}
if (!string.IsNullOrEmpty(query.order_date_pay_endTime.ToString()))
{
sbCondition.AppendFormat(" AND order_date_pay <= {0} ", CommonFunction.GetPHPTime(query.order_date_pay_endTime.ToString()));
}
if (query.delay == 0)//admin/Controller/DeliversController/all_in_1()方法，第443和458的判斷
{
sbCondition.Append(" AND order_status IN (0, 2, 3) ");
sbCondition.Append(" AND dm.deliver_id IS NOT NULL ");
}
else
{
sbCondition.Append(" AND dm.delivery_status IN (0,1, 2, 5) ");
}
sbCondition.Append(" AND dm.type IN (1, 2, 3, 4, 5, 6) ");
sbCondition.Append(" ORDER BY om.order_id ASC,dm.deliver_id ASC,v.vendor_id ASC ");
totalCount = 0;
if (query.IsPage)
{
System.Data.DataTable _dt = _dbAccess.getDataTable(" select count(1) as search_total " + sbTbls + sbCondition);
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["search_total"]);
}
sbCondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
strSql = sbCols.ToString() + sbTbls + sbCondition;
return _dbAccess.getDataTableForObj<OrderMasterQuery>(strSql);
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao.GetShipmentList-->" + ex.Message, ex);
}
}
public DataTable OrderSerchExport(OrderMasterQuery query)
{
DataTable _dt = new DataTable();
StringBuilder sql = new StringBuilder();
try
{
sql.Append("SELECT om.order_name,FROM_UNIXTIME(om.order_createdate) 'order_createdate',om.order_id,t_payment.parameterName 'order_payment', om.order_amount,t_order_status.remark 'order_status', od.item_id,");
sql.Append(" t_detail_status.remark 'detail_status',v.vendor_name_simple,v.vendor_code,od.product_name,od.buy_num,od.single_money,od.deduct_bonus,od.deduct_welfare,od.single_money*buy_num 'total_money' ,od.item_mode,od.parent_num, ");
sql.Append(" od.single_cost,od.bag_check_money,od.single_cost*od.buy_num 'total_cost' , FROM_UNIXTIME(os.slave_date_close) 'slave_date_close',mu.user_username as 'pm',om.source_trace as 'ID',redirect_name, t_product_mode.parameterName 'product_mode' ");
sql.Append(" from order_master om LEFT JOIN order_slave os ON om.order_id=os.order_id ");
sql.Append(" LEFT JOIN order_detail od ON os.slave_id=od.slave_id ");
sql.Append("LEFT JOIN vendor v ON v.vendor_id = od.item_vendor_id left join redirect r on r.redirect_id=om.source_trace ");
sql.Append(" LEFT JOIN redirect_group rg ON r.group_id = rg.group_id LEFT JOIN manage_user mu on mu.user_id=v.product_manage ");
sql.Append(" LEFT JOIN (select parameterCode,remark from t_parametersrc where parameterType='order_status') t_order_status on t_order_status.parameterCode=om.order_status ");
sql.Append(" LEFT JOIN (select parameterCode,remark from t_parametersrc where parameterType='order_status') t_detail_status on t_detail_status.parameterCode=od.detail_status ");
sql.Append(" LEFT JOIN (select parameterCode,parameterName from t_parametersrc where parameterType='payment') t_payment on t_payment.parameterCode=om.order_payment ");
sql.Append(" LEFT JOIN(select parameterCode,parameterName from t_parametersrc where parameterType='product_mode') t_product_mode on t_product_mode.parameterCode=od.product_mode ");
sql.AppendFormat(" where od.item_mode in (0,2) and om.order_date_pay<>0 and om.order_createdate>='{0}' and om.order_createdate<='{1}' order by om.order_id asc; ", CommonFunction.GetPHPTime(query.datestart.ToString()), CommonFunction.GetPHPTime(query.dateend.ToString()));
return _dbAccess.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->OrderSerchExport-->" + sql.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 查詢購買次數
/// </summary>
/// <param name="query">查詢條件</param>
/// <returns>購買次數</returns>
public int GetBuyCount(OrderMasterQuery query)
{
int buyCount = 0;
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder("SELECT count(1) as totalCount ");
StringBuilder sqlTable = new StringBuilder();
StringBuilder sqlCondition = new StringBuilder();
sqlTable.Append(" FROM order_master om ");
sqlTable.Append(" LEFT JOIN order_slave os ON os.order_id=om.order_id ");
sqlTable.Append(" LEFT JOIN order_detail od ON od.slave_id=os.slave_id ");
sqlCondition.Append(" WHERE 1=1 ");
if (query.User_Id != 0)
{
sqlCondition.AppendFormat(" AND om.user_id={0} ", query.User_Id);
}
if (query.Item_Id != 0)
{
sqlCondition.AppendFormat(" AND od.item_id={0} ", query.Item_Id);
}
try
{
sql.Append(sqlCount).Append(sqlTable).Append(sqlCondition);
DataTable dt = _dbAccess.getDataTable(sql.ToString());
if (dt.Rows.Count > 0)
{
buyCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
}
}
catch (Exception ex)
{
throw new Exception("BrowseDataDao-->GetBrowseDataList -->" + ex.Message + sql.ToString(), ex);
}
return buyCount;
}
/// <summary>
/// chaojie1124j add by 2015/12/14 04/46pm
/// 一個小時內之內數量過多訂單檢查
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable GetBigOrderNumbers(OrderMasterQuery query)
{
StringBuilder sb = new StringBuilder();
List<OrderMasterQuery> odMaster = new List<OrderMasterQuery>();
try
{
sb.AppendFormat(" SELECT count(1) as odcount,user_id from order_master where order_date_pay>'{0}' GROUP BY user_id ", CommonFunction.GetPHPTime(query.order_date_pay_startTime.ToString("yyyy/MM/dd HH:mm:ss")));
sb.Append(" HAVING odcount>(select parameterName from t_parametersrc where parameterType='auto_paramer' and parameterCode='order_count_limit' limit 1) ");
odMaster = _dbAccess.getDataTableForObj<OrderMasterQuery>(sb.ToString());
sb.Clear();
string user_id = "";
DataTable _dt=new DataTable();
if (odMaster.Count > 0)
{
for (int i = 0; i < odMaster.Count; i++)
{
user_id += odMaster[i].user_id + ",";
}
user_id=user_id.TrimEnd(',');
sb.AppendFormat(" SELECT us.user_name,us.user_email,om.order_ipfrom,om.order_id,FROM_UNIXTIME(om.order_date_pay)as new_time ");
sb.AppendFormat(" FROM order_master om LEFT JOIN users us on us.user_id=om.user_id ");
sb.AppendFormat(" WHERE om.order_date_pay>'{0}' and om.user_id in({1}) ", CommonFunction.GetPHPTime(query.order_date_pay_startTime.ToString("yyyy/MM/dd HH:mm:ss")), user_id);
_dt = _dbAccess.getDataTable(sb.ToString());
}
return _dt;
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->GetBigOrderNumbers-->" + sb.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 更新定單狀態以便開立發票
/// </summary>
/// <param name="order_id">定單編號</param>
/// <param name="freight_normal">常溫</param>
/// <param name="freight_low">低溫</param>
/// <param name="status">操作狀態</param>
/// <returns>是否操作成功</returns>
public bool ModifyOrderInvoice(int order_id, int freight_normal, int freight_low, string status)
{
//todo:對應Invoice.php頁面中第23行實現modify_order_invoice方法
bool flag = false;
OrderMasterQuery query = new OrderMasterQuery();
OrderMasterQuery model = new OrderMasterQuery();
query.Order_Id = Convert.ToUInt32(order_id);
if (status == "modify")
{
//設定條件 invoice_master_record.invoice_number='is not null'
query.status_description = "modify";
}
model = orderMasterDao.GetOrderMasterInvoice(query);
if (model != null)
{
if (model.Deduct_Card_Bonus > 0)
{
OrderDetailQuery queryOrderDetail = new OrderDetailQuery
{
Order_Id = Convert.ToUInt32(order_id)
};
DataTable dtAmount = orderDetailDao.GetBuyAmountAndTaxType(queryOrderDetail);
if (dtAmount!=null && dtAmount.Rows.Count>0)
{
int nTax = 0;
int nFreeTax = 0;
for (int i = 0; i < dtAmount.Rows.Count; i++)
{
if (dtAmount.Rows[i]["tax_type"]=="1")
{
//nTax=round
}
else if (dtAmount.Rows[i]["tax_type"]=="3")
{
}
}
}
}
}
return flag;
}
/// <summary>
/// chaojie1124j add by 2015/12/14 05/01pm
/// 用於實現大金額訂單檢查
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable GetBigAmount(OrderMasterQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append(" select order_id ,order_date_pay, order_amount from order_master where 1=1 ");
sb.Append(" and order_amount>(select parameterName from t_parametersrc where parameterType='auto_paramer' and parameterCode='order_amount_limit' ) ");
sb.AppendFormat(" and order_date_pay>'{0}' order by order_date_pay desc; ", CommonFunction.GetPHPTime(query.order_date_pay_startTime.ToString("yyyy/MM/dd HH:mm:ss")));
return _dbAccess.getDataTable(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->GetBigAmount-->" + sb.ToString() + ex.Message, ex);
}
}
public HttpResponseBase GetOrderList()
{
List<OrderMasterQuery> stores = new List<OrderMasterQuery>();
string json = string.Empty;
try
{
StringBuilder sb = new StringBuilder();
OrderMasterQuery query = new OrderMasterQuery();
DataTable dt_payment = new DataTable();
DataTable dt_status = new DataTable();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量
string addSerch = string.Empty;//追加一張表的查詢
int id; uint uid; DateTime dtime;
#region 查詢條件
if (int.TryParse(Request.Params["Vip_User_Group"].ToString(), out id))//會員群組
{
query.group_id = id;
}
if (int.TryParse(Request.Params["invoice"].ToString() ,out id ))//過濾已開發票
{
query.invoice=id;
}
if (uint.TryParse(Request.Params["channel"].ToString(),out uid))//賣場
{
query.Channel = uid;
}
if (int.TryParse(Request.Params["order_pay"].ToString(), out id))//付款狀態
{
query.pay_status = id;
}
if (!string.IsNullOrEmpty(Request.Params["selecttype"]))//查詢種類，訂單編號，訂購姓名
{
query.selecttype = Request.Params["selecttype"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["searchcon"]))//查詢內容
{
query.searchcon = Request.Params["searchcon"].ToString();
}
if (int.TryParse(Request.Params["timeone"].ToString(),out id))//時間類型
{
query.dateType = id;
}
if (DateTime.TryParse(Request.Params["dateOne"].ToString(), out dtime))//開始時間
{
query.datestart = dtime;
}
if (DateTime.TryParse(Request.Params["dateTwo"].ToString(), out dtime))
{
query.dateend = dtime;
}
if (int.TryParse(Request.Params["page_status"].ToString(),out id))//付款單狀態
{
query.orderStatus = Request.Params["page_status"];
}
if (uint.TryParse(Request.Params["order_payment"], out uid))//付款方式，AT
{
query.Order_Payment = uid;
}
#endregion
_ordermaster = new OrderMasterMgr(mySqlConnectionString);
int totalCount = 0;
stores = _ordermaster.getOrderSearch(query, sb.ToString(), out totalCount, addSerch);
dt_payment = GetTP("payment");
dt_status = GetTP("order_status");
foreach (var item in stores)
{
if (item.source_trace != 0)
{
item.redirect_name = "【" + item.source_trace + "】" + item.redirect_name;
}
string message = string.Empty;
//獲取時間
item.suser_reg_date = CommonFunction.GetNetTime(item.user_reg_date);
item.ordercreatedate = CommonFunction.GetNetTime(item.Order_Createdate);
if (item.Deduct_Bonus != 0)
{
message = "購物金使用:" + item.Deduct_Bonus;
}
if (item.Deduct_Welfare != 0)
{
message = "抵用劵使用:" + item.Deduct_Welfare;
}
if (item.Deduct_Happygo != 0)
{
message = "HG使用:" + item.Deduct_Happygo + "點/" + Math.Round(Convert.ToDecimal(item.Deduct_Happygo * item.deduct_happygo_convert)) + "元";//deduct_happygo_convert
}
item.order_pay_message = message;
if (item.Order_Payment >0)
{//帶出付款方式的參數
DataRow[] dr = dt_payment.Select("ParameterCode = '" + item.Order_Payment.ToString() + "'");
DataTable _newdt = dt_payment.Clone();
foreach (DataRow i in dr)
{
_newdt.Rows.Add(i.ItemArray);
}
item.payment = _newdt.Rows[0]["ParameterName"].ToString();
}
if (item.Order_Status >=0)
{//帶出付款方式的參數
DataRow[] dr = dt_status.Select("ParameterCode = '" + item.Order_Status.ToString() + "'");
DataTable _newdt = dt_status.Clone();
foreach (DataRow i in dr)
{
_newdt.Rows.Add(i.ItemArray);
}
item.orderStatus = _newdt.Rows[0]["remark"].ToString();
}
switch (item.Export_Flag)
{
case 0:
item.export_flag_str = "-";
break;
case 1:
item.export_flag_str = "待拋轉";
break;
case 2:
item.export_flag_str = "異動";
break;
case 3:
item.export_flag_str = "整筆取消";
break;
default:
item.export_flag_str = "已拋轉";
break;
}
}
#region 注釋 給訂單加上超鏈接/判斷購物金和抵用卷
//for (int i = 0; i < stores.Count; i++)
//{
//string message = string.Empty;
//DataRow[] rows = dt.Select("redirect_id=" + stores[i].Source_Trace);//篩選出訂單下的來源ID和url
//foreach (DataRow row in rows)//篩選出的最多只有一條數據，如果有，加入某個品牌的每日營業額，沒有就為初始值
//{
// if (!string.IsNullOrEmpty(row["redirect_id"].ToString()))
// {
// stores[i].ordercreatedate = CommonFunction.GetNetTime(stores[i].Order_Createdate);
// stores[i].redirect_name = row["redirect_name"].ToString();
// stores[i].redirect_url = row["redirect_url"].ToString();
// }
//}
//stores[i].ordercreatedate = CommonFunction.GetNetTime(stores[i].Order_Createdate);
//if (stores[i].Deduct_Bonus != 0)
//{
// message = "購物金使用:" + stores[i].Deduct_Bonus;
//}
//if (stores[i].Deduct_Welfare != 0)
//{
// message = "抵用卷使用使用:" + stores[i].Deduct_Welfare;
//}
//if (stores[i].Deduct_Happygo != 0)
//{
// message = "HG使用:" + stores[i].Deduct_Happygo + "點/" + Math.Round(Convert.ToDecimal(stores[i].Deduct_Happygo * stores[i].Deduct_Happygo_Convert)) + "元";
//}
//stores[i].order_pay_message = message;
//}
#endregion
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,'msg':'user',totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, 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;
}
/// <summary>
/// chaojie1124j add by 2015/12/14 05/04pm
/// 首購超過5000檢查
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable GetUsersOrderAmount(OrderMasterQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append(@"select om.order_id,om.order_date_pay,om.order_amount,FROM_UNIXTIME(om.money_collect_date) as collect_date,FROM_UNIXTIME(u.first_time) as first_time from order_master om INNER JOIN users u ON om.user_id=u.user_id ");
sb.AppendFormat(" WHERE order_amount>=5000 AND order_date_pay >'{0}' ", CommonFunction.GetPHPTime(query.order_date_pay_startTime.ToString("yyyy/MM/dd HH:mm:ss")));
sb.AppendFormat(" HAVING DATE_FORMAT(collect_date,'%Y-%m-%d')=DATE_FORMAT(first_time,'%Y-%m-%d') order by order_date_pay desc ");
return _dbAccess.getDataTable(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->GetUsersOrderAmount-->" + sb.ToString() + ex.Message, ex);
}
}
public void ExportReport()
{
List<OrderMasterQuery> stores = new List<OrderMasterQuery>();
OrderMasterQuery query = new OrderMasterQuery();
DateTime dtime;
string payment = string.Empty;
string order_status = string.Empty;
string detail_status = string.Empty;
string product_mode = string.Empty;
string total_amount = string.Empty;
string invoice_number = string.Empty;
string invoice_date = string.Empty;
try
{
_orderMasterMgr = new OrderMasterMgr(mySqlConnectionString);
_orderReturnStatus = new OrderReturnStatusMgr(mySqlConnectionString);
#region 查詢條件
string dateOne = Request.Params["dateOne"];
string dateTwo = Request.Params["dateTwo"];
if (DateTime.TryParse(Request.Params["dateOne"].ToString(), out dtime))//開始時間
{
query.datestart = dtime;
}
if (DateTime.TryParse(Request.Params["dateTwo"].ToString(), out dtime))
{
query.dateend = dtime;
}
#endregion
DataTable dtHZ = new DataTable();
dtHZ.Columns.Add("會員姓名", typeof(String));
dtHZ.Columns.Add("購買時間", typeof(String));
dtHZ.Columns.Add("付款單號", typeof(String));
dtHZ.Columns.Add("付款方式", typeof(String));
dtHZ.Columns.Add("購買金額", typeof(String));
dtHZ.Columns.Add("付款狀態", typeof(String));
dtHZ.Columns.Add("發票號碼", typeof(String));
dtHZ.Columns.Add("發票金額", typeof(String));
dtHZ.Columns.Add("發票開立日期", typeof(String));
dtHZ.Columns.Add("商品細項編號", typeof(String));
dtHZ.Columns.Add("訂單狀態", typeof(String));
dtHZ.Columns.Add("供應商", typeof(String));
dtHZ.Columns.Add("供應商編碼", typeof(String));
dtHZ.Columns.Add("品名", typeof(String));
dtHZ.Columns.Add("數量", typeof(String));
dtHZ.Columns.Add("購買單價", typeof(String));
dtHZ.Columns.Add("折抵購物金", typeof(String));
dtHZ.Columns.Add("抵用卷", typeof(String));
dtHZ.Columns.Add("總價", typeof(String));
dtHZ.Columns.Add("成本單價", typeof(String));
dtHZ.Columns.Add("寄倉費", typeof(String));
dtHZ.Columns.Add("成本總額", typeof(String));
dtHZ.Columns.Add("出貨單歸檔期", typeof(String));
dtHZ.Columns.Add("負責PM", typeof(String));
dtHZ.Columns.Add("來源ID", typeof(String));
dtHZ.Columns.Add("來源名稱", typeof(String));
dtHZ.Columns.Add("出貨方式", typeof(String));
DataTable _dt = _orderMasterMgr.OrderSerchExport(query);
for (int i = 0; i < _dt.Rows.Count; i++)
{
if (i != 0)
{
if (_dt.Rows[i]["order_id"].ToString() == _dt.Rows[i - 1]["order_id"].ToString())
{
dtHZ.Rows.Add(_orderReturnStatus.GetOrdinaryData(dtHZ, _dt, i));
}
else
{
if (i == _dt.Rows.Count - 1)
{
#region 如果此order_id是最後一條數據
dtHZ.Rows.Add(_orderReturnStatus.GetOrdinaryData(dtHZ, _dt, i));
DataTable _dtFreight = _orderMasterMgr.GetOrderFreight(Convert.ToUInt32(_dt.Rows[i]["order_id"]));
if (_dtFreight != null && _dtFreight.Rows.Count > 0)
{
#region 常溫運費
if (Convert.ToInt32(_dtFreight.Rows[0][0]) != 0)//常溫運費
{
dtHZ.Rows.Add(_orderReturnStatus.GetFreightData(dtHZ, _dt, _dtFreight, i,1));
}
#endregion
#region 低溫運費
if (Convert.ToInt32(_dtFreight.Rows[0][1]) != 0)//低溫運費
{
dtHZ.Rows.Add(_orderReturnStatus.GetFreightData(dtHZ, _dt, _dtFreight, i, 2));
}
#endregion
}
#endregion
}
else
{
#region 查上一個order_id是否有運費
DataTable _dtFreight02 = _orderMasterMgr.GetOrderFreight(Convert.ToUInt32(_dt.Rows[i - 1]["order_id"]));
if (_dtFreight02 != null && _dtFreight02.Rows.Count > 0)
{
#region 常溫運費
if (Convert.ToInt32(_dtFreight02.Rows[0][0]) != 0)//常溫運費
{
dtHZ.Rows.Add(_orderReturnStatus.GetFreightData(dtHZ, _dt, _dtFreight02, i - 1,1));
}
#endregion
#region 低溫運費
if (Convert.ToInt32(_dtFreight02.Rows[0][1]) != 0)//低溫運費
{
dtHZ.Rows.Add(_orderReturnStatus.GetFreightData(dtHZ, _dt, _dtFreight02, i - 1,2));
}
#endregion
}
#endregion
#region 查當前order_id
dtHZ.Rows.Add(_orderReturnStatus.GetOrdinaryData(dtHZ, _dt, i));
#endregion
}
}
}
else
{
dtHZ.Rows.Add(_orderReturnStatus.GetOrdinaryData(dtHZ, _dt, i));
}
}
string fileName = "會計總表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "");
Response.AddHeader("Content-Disposition", "attach-ment;filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
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);
}
}
/// <summary>
/// chaojie1124j add by 2015/12/14 05/14pm
/// 異地付款訂單檢查
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable GetOtherTWPay(OrderMasterQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append(@"SELECT om.order_id,om.order_amount,om.order_date_pay,om.order_ipfrom,opc.pan from order_payment_nccc opc INNER JOIN order_master om on opc.orderid=om.order_id ");
sb.AppendFormat(@"where opc.nccc_createdate>{0} and opc.transcode='00' and opc.responsecode='00' ", CommonFunction.GetPHPTime(query.order_date_pay_startTime.ToString("yyyy/MM/dd HH:mm:ss")));
sb.Append(" and opc.pan is not NULL and opc.pan <>'' and (opc.bankname ='' or opc.bankname is null) ");
sb.Append(" union SELECT om.order_id,om.order_amount,money_collect_date,om.order_ipfrom, oph.pan from order_payment_hitrust oph ");
sb.AppendFormat(" INNER JOIN order_master om on oph.order_id=om.order_id where oph.updatetime>'{0}' ",CommonFunction.GetPHPTime(query.order_date_pay_endTime.ToString("yyyy/MM/dd HH:mm:ss")));
sb.Append(" and oph.retstatus=1 and oph.pan is NULL and oph.pan <> '' and (oph.bankname<>'' or oph.bankname is null) ");
return _dbAccess.getDataTable(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->GetOtherTWPay-->" + sb.ToString() + ex.Message, ex);
}
}
public HttpResponseBase ChangePayMent()
{
string json = string.Empty;
OrderMasterQuery query = new OrderMasterQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["payment"]))
{
query.payment = Request.Params["payment"];
}
if (!string.IsNullOrEmpty(Request.Params["delivery"]))
{
query.delivery = Convert.ToInt32(Request.Params["delivery"]);
}
if (!string.IsNullOrEmpty(Request.Params["order_id"]))
{
query.Order_Id = Convert.ToUInt32(Request.Params["order_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["delivery_store"]))
{
query.Delivery_Store = Convert.ToUInt32(Request.Params["delivery_store"]);
}
System.Net.IPAddress[] addlist = Dns.GetHostByName(Dns.GetHostName()).AddressList;
query.Order_Ipfrom = addlist[0].ToString();
query.username = (Session["caller"] as Caller).user_username;
_orderMasterMgr = new OrderMasterMgr(mySqlConnectionString);
json= _orderMasterMgr.ChangePayMent(query);
}
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:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 訂單查詢sql
/// </summary>
/// <param name="query"></param>
/// <param name="sqladdstr"></param>
/// <param name="totalCount"></param>
/// <param name="addSerch"></param>
/// <returns></returns>
public List<OrderMasterQuery> getOrderSearch(OrderMasterQuery query, string sqladdstr, out int totalCount, string addSerch)
{
StringBuilder sqlClm = new StringBuilder();
StringBuilder sqljoin = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
try
{
sqlClm.AppendFormat(@"SELECT DISTINCT om.order_id,u.user_id,channel_name_simple as channel_name_full,om.deduct_happygo_convert,om.export_flag,om.order_name,om.delivery_name,om.order_amount,om.order_payment,om.order_status,om.order_createdate,om.channel as channel_id,om.note_admin,om.source_trace,om.deduct_welfare,om.deduct_happygo,om.deduct_bonus,u.user_email,imr.invoice_number,redirect.redirect_name as redirect_name,redirect.redirect_url as redirect_url,u.user_id,user_password,user_name,user_gender,user_mobile,user_phone,user_zip,user_address,user_reg_date , CASE user_type when '1' THEN '網路會員' else'電話會員' END as mytype ,send_sms_ad,adm_note ,concat(user_birthday_year,'/',user_birthday_month,'/',user_birthday_day) as birthday");
string sqlcount = "select count(DISTINCT om.order_id) AS search_total ";
sqljoin.AppendFormat(@" FROM order_master om INNER JOIN users u ON u.user_id = om.user_id LEFT JOIN invoice_master_record imr ON om.order_id = imr.order_id LEFT JOIN channel on om.channel=channel.channel_id left join redirect on redirect.redirect_id=om.source_trace");
sqljoin.AppendFormat(" LEFT JOIN deliver_master dm ON dm.order_id = om.order_id ");
sqlwhere.AppendFormat(" WHERE om.user_id = u.user_id ");
#region where
if (query.group_id > 0)
{
sqlwhere.AppendFormat(" AND vu.group_id = '{0}'", query.group_id);
sqljoin.Append(" INNER JOIN vip_user vu ON om.user_id = vu.user_id ");
}
if (query.invoice != 0)
{
sqlwhere.AppendFormat(" and imr.invoice_id IS NULL AND (om.money_cancel + om.money_return) != om.order_amount");
}
if (query.Channel > 0)
{
sqlwhere.AppendFormat(" AND om.channel = '{0}'", query.Channel);
}
if (query.pay_status > 0)
{
switch (query.pay_status)
{
case 1:
sqlwhere.AppendFormat(" and om.money_collect_date <> 0 ");
break;
case 2:
sqlwhere.AppendFormat(" and om.money_collect_date = 0 ");
break;
default:
break;
}
}
if (!string.IsNullOrEmpty(query.searchcon))
{
switch (query.selecttype)
{
case "1":
sqlwhere.AppendFormat(" AND om.order_id LIKE '%{0}%'", query.searchcon);
break;
case "2":
sqlwhere.AppendFormat(" AND om.order_name LIKE '%{0}%'", query.searchcon);
break;
case "3":
sqlwhere.AppendFormat(" AND u.user_email LIKE '%{0}%'", query.searchcon);
break;
case "4":
sqlwhere.AppendFormat(" AND om.delivery_name LIKE '%{0}%'", query.searchcon);
break;
case "5":
sqlwhere.AppendFormat(" AND om.source_trace LIKE '%{0}%'", query.searchcon);
break;
case "6":
sqlwhere.AppendFormat(" AND om.order_mobile LIKE '%{0}%'", query.searchcon);
break;
case "7":
sqlwhere.AppendFormat(" AND om.channel_order_id LIKE '%{0}%'", query.searchcon);
break;
case "8":
sqlwhere.AppendFormat(" AND om.delivery_mobile LIKE '%{0}%'", query.searchcon);
break;
case "9":
sqlwhere.AppendFormat(" AND (om.order_address LIKE '%{0}%' or om.delivery_address LIKE '%{0}%') ", query.searchcon);
break;
case "10":
sqlwhere.AppendFormat(" AND ( om.order_phone LIKE '%{0}%' or m.delivery_phone LIKE '%{0}%') ", query.searchcon);
break;
case "11":
sqlwhere.AppendFormat(" AND dm.delivery_code LIKE '%{0}%'", query.searchcon);
break;
default:
break;
}
}
switch (query.dateType)
{
case 1:
if (query.datestart > DateTime.MinValue && query.dateend > DateTime.MinValue)
{
sqlwhere.AppendFormat(" AND om.order_createdate >= '{0}' ", CommonFunction.GetPHPTime(query.datestart.ToString("yyyy-MM-dd HH:mm:ss")));
sqlwhere.AppendFormat(" AND om.order_createdate <= '{0}'", CommonFunction.GetPHPTime(query.dateend.ToString("yyyy-MM-dd HH:mm:ss")));
}
break;
case 2:
if (query.datestart > DateTime.MinValue && query.dateend > DateTime.MinValue)
{
sqlwhere.AppendFormat(" AND (om.order_createdate >= '{0}' ", CommonFunction.GetPHPTime(query.datestart.ToString("yyyy-MM-dd HH:mm:ss")));
sqlwhere.AppendFormat(" AND om.order_createdate <= '{0}' or", CommonFunction.GetPHPTime(query.dateend.ToString("yyyy-MM-dd HH:mm:ss")));
sqlwhere.AppendFormat(" om.money_collect_date >= '{0}' ", CommonFunction.GetPHPTime(query.datestart.ToString("yyyy-MM-dd HH:mm:ss")));
sqlwhere.AppendFormat(" AND om.money_collect_date <= '{0}') ", CommonFunction.GetPHPTime(query.dateend.ToString("yyyy-MM-dd HH:mm:ss")));
}
break;
default:
break;
}
if (query.dateType == 1)
{
if (query.datestart > DateTime.MinValue)
{
sqlwhere.AppendFormat(" AND om.order_createdate >= '{0}' ", CommonFunction.GetPHPTime(query.datestart.ToString("yyyy-MM-dd HH:mm:ss")));
}
if (query.dateend > DateTime.MinValue)
{
sqlwhere.AppendFormat(" AND om.order_createdate <= '{0}'", CommonFunction.GetPHPTime(query.dateend.ToString("yyyy-MM-dd HH:mm:ss")));
}
}
if (!string.IsNullOrEmpty(query.orderStatus) && query.orderStatus != "-1")
{
sqlwhere.AppendFormat(" and om.order_status = {0} ", query.orderStatus);
// AND imr.invoice_id IS NULL AND (om.money_cancel + om.money_return) != om.order_amount
}
if (query.Order_Payment > 0)
{
sqlwhere.AppendFormat(" and om.order_payment={0}", query.Order_Payment);
}
#endregion
//sqljoin.AppendFormat(sqladdstr);
totalCount = 0;
if (query.IsPage)
{
System.Data.DataTable _dt = _dbAccess.getDataTable(sqlcount + sqljoin.ToString() + sqlwhere.ToString());
if (Convert.ToInt32(_dt.Rows[0][0]) > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["search_total"]);
}
sqlwhere.AppendFormat(" GROUP BY om.order_id ORDER BY om.order_id DESC limit {0},{1}", query.Start, query.Limit);
}
return _dbAccess.getDataTableForObj<OrderMasterQuery>(sqlClm.ToString() + sqljoin.ToString() + sqlwhere.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao.getOrderSearch -->" + ex.Message + sqlClm.ToString() + sqljoin.ToString() + sqlwhere.ToString(), ex);
}
}
public string GetBigOrderNumbersMailBody(OrderMasterQuery query,out bool isShow)
{
isShow = false;
StringBuilder strbody = new StringBuilder();
strbody.AppendLine("一小時之內數量過多訂單檢查:");
DataTable _newErrorDt = new DataTable();
_newErrorDt.Columns.Add("訂購人", typeof(String));
_newErrorDt.Columns.Add("郵箱", typeof(String));
_newErrorDt.Columns.Add("IP地址", typeof(String));
_newErrorDt.Columns.Add("訂單編號", typeof(String));
_newErrorDt.Columns.Add("付款時間", typeof(String));
_orderMaster = new OrderMasterMgr(mySqlConnectionString);
DataTable _dtResult = _orderMaster.GetBigOrderNumbers(query);
if (_dtResult.Rows.Count > 0)
{
for (int j = 0; j < _dtResult.Rows.Count; j++)
{
DataRow drtwo = _newErrorDt.NewRow();
drtwo[0] = _dtResult.Rows[j][0];
drtwo[1] = _dtResult.Rows[j][1];
drtwo[2] = _dtResult.Rows[j][2];
drtwo[3] = _dtResult.Rows[j][3];
drtwo[4] = DateTime.Parse(_dtResult.Rows[j][4].ToString()).ToString("yyyy/MM/dd HH:mm:ss");
_newErrorDt.Rows.Add(drtwo);
}
isShow = true;
}
strbody.AppendFormat(GetHtmlByDataTable(_newErrorDt));
return strbody.ToString();
}
/// <summary>
/// 訂單匯出Excel-sql
/// </summary>
/// <param name="query"></param>
/// <param name="sqladdstr"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<OrderMasterQuery> Export(OrderMasterQuery query, string sqladdstr, out int totalCount)
{
StringBuilder sqlClm = new StringBuilder();
StringBuilder sqljoin = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
try
{
sqlClm.AppendFormat(@" SELECT DISTINCT om.deduct_card_bonus,om.money_cancel,om.money_return,om.deduct_happygo_convert,om.order_date_pay,u.user_id,om.order_freight_low,om.order_freight_normal,om.order_mobile,om.order_id,channel_name_simple as channel_name_full,om.order_name,om.delivery_name,om.order_amount,om.order_payment,om.order_status,om.order_createdate, ");
sqlClm.AppendFormat(" om.note_admin,om.deduct_welfare,om.deduct_happygo,om.deduct_bonus ");
sqlClm.AppendFormat(" ,u.user_email,redirect.redirect_name as redirect_name,ct.offsetamt ,rg.group_name,td.utm_id,td.utm_source,oph.hncb_id,om.source_trace,om.channel_order_id ");//imr.invoice_number,
sqljoin.AppendFormat(@" FROM order_master om INNER JOIN users u ON u.user_id = om.user_id
LEFT JOIN channel on om.channel=channel.channel_id left join redirect on redirect.redirect_id=om.source_trace
LEFT JOIN order_payment_ct ct ON ct.lidm = om.order_id LEFT JOIN redirect_group rg ON redirect.group_id = rg.group_id LEFT JOIN track_detail td ON om.source_cookie_value = td.detail_id
LEFT JOIN order_payment_hncb oph ON oph.order_id = om.order_id LEFT JOIN deliver_master dm ON dm.order_id = om.order_id ");// ,om.source_trace,om.channel as channel_id,
sqlwhere.AppendFormat(" WHERE 1=1 and om.user_id = u.user_id ");
#region where
if (query.group_id > 0)
{
sqlwhere.AppendFormat(" AND vu.group_id = '{0}'", query.group_id);
sqljoin.Append(" INNER JOIN vip_user vu ON om.user_id = vu.user_id ");
}
if (query.invoice != 0)
{
sqlwhere.AppendFormat(" and imr.invoice_id IS NULL AND (om.money_cancel + om.money_return) != om.order_amount");
sqljoin.Append(" LEFT JOIN invoice_master_record imr ON om.order_id = imr.order_id ");
}
if (query.Channel > 0)
{
sqlwhere.AppendFormat(" AND om.channel = '{0}'", query.Channel);
}
if (query.pay_status > 0)
{
switch (query.pay_status)
{
case 1:
sqlwhere.AppendFormat(" and om.money_collect_date <> 0 ");
break;
case 2:
sqlwhere.AppendFormat(" and om.money_collect_date = 0 ");
break;
default:
break;
}
}
if (!string.IsNullOrEmpty(query.searchcon))
{
switch (query.selecttype)
{
case "1":
sqlwhere.AppendFormat(" AND om.order_id LIKE '%{0}%'", query.searchcon);
break;
case "2":
sqlwhere.AppendFormat(" AND om.order_name LIKE '%{0}%'", query.searchcon);
break;
case "3":
sqlwhere.AppendFormat(" AND u.user_email LIKE '%{0}%'", query.searchcon);
break;
case "4":
sqlwhere.AppendFormat(" AND om.delivery_name LIKE '%{0}%'", query.searchcon);
break;
case "5":
sqlwhere.AppendFormat(" AND om.source_trace LIKE '%{0}%'", query.searchcon);
break;
case "6":
sqlwhere.AppendFormat(" AND om.order_mobile LIKE '%{0}%'", query.searchcon);
break;
case "7":
sqlwhere.AppendFormat(" AND om.channel_order_id LIKE '%{0}%'", query.searchcon);
break;
case "8":
sqlwhere.AppendFormat(" AND om.delivery_mobile LIKE '%{0}%'", query.searchcon);
break;
case "9":
sqlwhere.AppendFormat(" AND (om.order_address LIKE '%{0}%' or om.delivery_address LIKE '%{0}%') ", query.searchcon);
break;
case "10":
sqlwhere.AppendFormat(" AND ( om.order_phone LIKE '%{0}%' or m.delivery_phone LIKE '%{0}%') ", query.searchcon);
break;
case "11":
sqlwhere.AppendFormat(" AND dm.delivery_code LIKE '%{0}%'", query.searchcon);
break;
default:
break;
}
}
switch (query.dateType)
{
case 1:
if (query.datestart > DateTime.MinValue && query.dateend > DateTime.MinValue)
{
sqlwhere.AppendFormat(" AND om.order_createdate >= '{0}' ", CommonFunction.GetPHPTime(query.datestart.ToString("yyyy-MM-dd HH:mm:ss")));
sqlwhere.AppendFormat(" AND om.order_createdate <= '{0}'", CommonFunction.GetPHPTime(query.dateend.ToString("yyyy-MM-dd HH:mm:ss")));
}
break;
case 2:
if (query.datestart > DateTime.MinValue && query.dateend > DateTime.MinValue)
{
sqlwhere.AppendFormat(" AND (om.order_createdate >= '{0}' ", CommonFunction.GetPHPTime(query.datestart.ToString("yyyy-MM-dd HH:mm:ss")));
sqlwhere.AppendFormat(" AND om.order_createdate <= '{0}' or", CommonFunction.GetPHPTime(query.dateend.ToString("yyyy-MM-dd HH:mm:ss")));
sqlwhere.AppendFormat(" om.money_collect_date >= '{0}' ", CommonFunction.GetPHPTime(query.datestart.ToString("yyyy-MM-dd HH:mm:ss")));
sqlwhere.AppendFormat(" AND om.money_collect_date <= '{0}') ", CommonFunction.GetPHPTime(query.dateend.ToString("yyyy-MM-dd HH:mm:ss")));
}
break;
default:
break;
}
if (!string.IsNullOrEmpty(query.orderStatus) && query.orderStatus != "-1")
{
sqlwhere.AppendFormat(" and om.order_status = {0} ", query.orderStatus);
// AND imr.invoice_id IS NULL AND (om.money_cancel + om.money_return) != om.order_amount
}
if (query.Order_Payment > 0)
{
sqlwhere.AppendFormat(" and om.order_payment={0}", query.Order_Payment);
}
#endregion
//sqljoin.AppendFormat(sqladdstr);
sqlwhere.AppendFormat(" ORDER BY om.order_id DESC");
totalCount = 0;
return _dbAccess.getDataTableForObj<OrderMasterQuery>(sqlClm.ToString() + sqljoin.ToString() + sqlwhere.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao.Export -->" + ex.Message + sqlClm.ToString() + sqljoin.ToString() + sqlwhere.ToString(), ex);
}
}
public string OtherTWPay(OrderMasterQuery query,out bool isShow)
{
isShow = false;
StringBuilder strbody = new StringBuilder();
strbody.AppendLine("異地付款訂單檢查");
_orderMaster = new OrderMasterMgr(mySqlConnectionString);
DataTable _dt = _orderMaster.GetOtherTWPay(query);
if (_dt.Rows.Count > 0)
{
DataTable _newdt = new DataTable();
_newdt.Columns.Add("訂單編號", typeof(String));
_newdt.Columns.Add("付款總金額", typeof(String));
_newdt.Columns.Add("付款時間", typeof(String));
_newdt.Columns.Add("IP", typeof(String));
for (int i = 0; i < _dt.Rows.Count; i++)
{
DataRow dr = _newdt.NewRow();
dr[0] = _dt.Rows[i][0];
dr[1] = _dt.Rows[i][1].ToString();
dr[2] = CommonFunction.GetNetTime(Convert.ToInt64(_dt.Rows[i][2])).ToString("yyyy/MM/dd HH:mm:ss");
dr[3] = _dt.Rows[i][3].ToString();
_newdt.Rows.Add(dr);
}
strbody.AppendFormat(GetHtmlByDataTable(_newdt));
isShow = true;
}
return strbody.ToString();
}
/// <summary>
/// 獲取需要開立發票的訂單
/// </summary>
/// <param name="query">查詢條件</param>
/// <returns>需要開立發票的訂單</returns>
public OrderMasterQuery GetOrderMasterInvoice(OrderMasterQuery query)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("SELECT om.order_id,om.order_payment,om.order_freight_normal,om.order_freight_low,om.deduct_happygo_convert,om.bonus_type,om.company_invoice,om.company_title,om.order_name,om.order_zip,om.order_address,om.invoice_status,imr.invoice_number,om.deduct_card_bonus ");
sbSql.Append(" FROM order_master om ");
sbSql.Append(" LEFT JOIN invoice_master_record imr ON om.order_id=imr.order_id ");
sbSql.Append(" WHERE 1=1 ");
sbSql.AppendFormat(" AND om.order_id={0} ", query.Order_Id);
sbSql.Append(" AND om.order_amount>0 ");
if (query.status_description == "modify")
{
sbSql.Append(" AND imr.invoice_number IS NOT NULL ");
}
try
{
return _dbAccess.getSinggleObj<OrderMasterQuery>(sbSql.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->GetOrderMasterInvoice-->" + ex.Message + sbSql.ToString(), ex);
}
}