public HttpResponseBase GetIpodListNo()
{
IpodQuery ipod = new IpodQuery();
string json = string.Empty;
ipod.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
ipod.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Params["freight"]))
{
ipod.product_freight_set = int.Parse(Request.Params["freight"]);
}
if (!string.IsNullOrEmpty(Request.Params["updateuser"]))
{
ipod.change_user = int.Parse(Request.Params["updateuser"]);
}
if (!string.IsNullOrEmpty(Request.Params["erp_id"]))
{
ipod.Erp_Id = Request.Params["erp_id"];
}
if (!string.IsNullOrEmpty(Request.Params["Potype"]))
{
ipod.po_type = Request.Params["Potype"];//類別
}
if (!string.IsNullOrEmpty(Request.Params["vendor_id"]))
{
UInt64 vendorid = 0;
if (UInt64.TryParse(Request.Params["vendor_id"], out vendorid))
{
ipod.vendor_id = vendorid;
}
}
if (!string.IsNullOrEmpty(Request.Params["vendor_name_full"]))
{
string vendorName = Request.Params["vendor_name_full"].ToString();
int index1 = vendorName.IndexOf('%');
int index2 = vendorName.IndexOf('_');
if (index1 != -1)
{
string start = vendorName.Substring(0, index1);
string end = vendorName.Substring(index1 + 1);
vendorName = start + "/" + "%" + end;
}
if (index2 != -1)
{
string start = vendorName.Substring(0, index2);
string end = vendorName.Substring(index2 + 1);
vendorName = start + "/" + "_" + end;
}
ipod.vendor_name_full = vendorName;
}
if (!string.IsNullOrEmpty(Request.Params["check"]))
{
bool check = true;
if (bool.TryParse(Request.Params["check"], out check))
{
ipod.Check = check;
}
}
if (!string.IsNullOrEmpty(Request.Params["product_name"]))
{
string product_name = Request.Params["product_name"].ToString();
int index1 = product_name.IndexOf('%');
int index2 = product_name.IndexOf('_');
if (index1 != -1)
{
string start = product_name.Substring(0, index1);
string end = product_name.Substring(index1 + 1);
product_name = start + "/" + "%" + end;
}
if (index2 != -1)
{
string start = product_name.Substring(0, index2);
string end = product_name.Substring(index2 + 1);
product_name = start + "/" + "_" + end;
}
ipod.product_name = product_name;
}
if (!string.IsNullOrEmpty(Request.Params["product_id"]))
{
uint product_id = 0;
if (uint.TryParse(Request.Params["product_id"], out product_id))
{
ipod.product_id = product_id;
}
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
ipod.start_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["start_time"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
ipod.end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["end_time"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
}
try
{
List<IpodQuery> store = new List<IpodQuery>();
_ipodMgr = new IpodMgr(mySqlConnectionString);
int totalCount = 0;
store = _ipodMgr.GetIpodListNo(ipod, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,'msg':'user',totalCount:" + totalCount + ",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:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public void WriteExcel()
{
try
{
IpodQuery ipod = new IpodQuery();
List<IpodQuery> ipoStore = new List<IpodQuery>();
if (!string.IsNullOrEmpty(Request.Params["freight"]))
{
ipod.product_freight_set = int.Parse(Request.Params["freight"]);
}
if (!string.IsNullOrEmpty(Request.Params["updateuser"]))
{
ipod.change_user = int.Parse(Request.Params["updateuser"]);
}
if (!string.IsNullOrEmpty(Request.Params["erp_id"]))
{
ipod.Erp_Id = Request.Params["erp_id"];
}
if (!string.IsNullOrEmpty(Request.Params["Potype"]))
{
ipod.po_type = Request.Params["Potype"];//類別
}
if (Request.Params["vendor_id"] != "null")
{
ipod.vendor_id = uint.Parse(Request.Params["vendor_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["vendor_name_full"]))
{
string vendorName = Request.Params["vendor_name_full"].ToString();
int index1 = vendorName.IndexOf('%');
int index2 = vendorName.IndexOf('_');
if (index1 != -1)
{
string start = vendorName.Substring(0, index1);
string end = vendorName.Substring(index1 + 1);
vendorName = start + "/" + "%" + end;
}
if (index2 != -1)
{
string start = vendorName.Substring(0, index2);
string end = vendorName.Substring(index2 + 1);
vendorName = start + "/" + "_" + end;
}
ipod.vendor_name_full = vendorName;
}
if (!string.IsNullOrEmpty(Request.Params["check"]))
{
bool check = true;
if (bool.TryParse(Request.Params["check"], out check))
{
ipod.Check = check;
}
}
if (!string.IsNullOrEmpty(Request.Params["product_name"]))
{
string product_name = Request.Params["product_name"].ToString();
int index1 = product_name.IndexOf('%');
int index2 = product_name.IndexOf('_');
if (index1 != -1)
{
string start = product_name.Substring(0, index1);
string end = product_name.Substring(index1 + 1);
product_name = start + "/" + "%" + end;
}
if (index2 != -1)
{
string start = product_name.Substring(0, index2);
string end = product_name.Substring(index2 + 1);
product_name = start + "/" + "_" + end;
}
ipod.product_name = product_name;
}
if (!string.IsNullOrEmpty(Request.Params["product_id"]))
{
uint product_id = 0;
if (uint.TryParse(Request.Params["product_id"], out product_id))
{
ipod.product_id = product_id;
}
}
DateTime date;
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
if (DateTime.TryParse(Request.Params["start_time"].ToString(), out date))
{
ipod.start_time = Convert.ToDateTime(date.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
if (DateTime.TryParse(Request.Params["end_time"].ToString(), out date))
{
ipod.end_time = Convert.ToDateTime(date.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
ipod.IsPage = false;
_ipodMgr = new IpodMgr(mySqlConnectionString);
int totalCount = 0;
ipoStore = _ipodMgr.GetIpodListNo(ipod, out totalCount);
DataTable _newDt = new DataTable();
_newDt.Columns.Add("採購單別", typeof(string));
_newDt.Columns.Add("採購單號", typeof(string));
_newDt.Columns.Add("供應商編號", typeof(string));
_newDt.Columns.Add("供應商名稱", typeof(string));
_newDt.Columns.Add("品號", typeof(string));
_newDt.Columns.Add("商品編號", typeof(string));
_newDt.Columns.Add("商品細項編號", typeof(string));
_newDt.Columns.Add("商品名稱", typeof(string));
_newDt.Columns.Add("溫層", typeof(string));
_newDt.Columns.Add("規格", typeof(string));
_newDt.Columns.Add("採購數量", typeof(string));
_newDt.Columns.Add("允收數量", typeof(string));
_newDt.Columns.Add("不允收量", typeof(string));
_newDt.Columns.Add("創建時間", typeof(string));
_newDt.Columns.Add("創建人", typeof(string));
_newDt.Columns.Add("異動時間", typeof(string));
_newDt.Columns.Add("異動人", typeof(string));
for (int i = 0; i < ipoStore.Count; i++)
{
DataRow newRow = _newDt.NewRow();
newRow["採購單別"] = ipoStore[i].parameterName;
newRow["採購單號"] = ipoStore[i].po_id + " ";
newRow["品號"] = ipoStore[i].Erp_Id + " ";
newRow["商品名稱"] = ipoStore[i].product_name;
newRow["供應商名稱"] = ipoStore[i].vendor_name_full;
newRow["規格"] = ipoStore[i].spec;
newRow["採購數量"] = ipoStore[i].qty_ord;
newRow["允收數量"] = ipoStore[i].qty_claimed;
newRow["不允收量"] = ipoStore[i].qty_damaged;
newRow["商品細項編號"] = ipoStore[i].item_id;
newRow["商品編號"] = ipoStore[i].productid;
newRow["供應商編號"] = ipoStore[i].vendor_id;
newRow["創建時間"] = ipoStore[i].create_dtim.ToString("yyyy-MM-dd HH:mm:ss");
newRow["創建人"] = ipoStore[i].create_username;
newRow["異動時間"] = ipoStore[i].change_dtim.ToString("yyyy-MM-dd HH:mm:ss");
newRow["異動人"] = ipoStore[i].change_username;
newRow["溫層"] = ipoStore[i].product_freight_set == 1 ? "常溫" : "冷凍";
_newDt.Rows.Add(newRow);
}
string fileName = string.Empty;
if (ipod.Check)
{
fileName = "採購單驗收不符報表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
}
else
{
fileName = "採購單驗收報表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
}
MemoryStream ms = ExcelHelperXhf.ExportDT(_newDt, "");
Response.AddHeader("Content-Disposition", "attachment; 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);
}
}
