/// <summary>
///
/// </summary>
/// <param name="warehouseID"></param>
/// <returns></returns>
public HttpResponseMessage Get()
{
HttpResponseMessage response = new HttpResponseMessage();
//_logger.Debug("Download 參數 warehouseID:{0}", warehouseID);
var isDebug = bool.Parse(CommonUtils.AppSettings("IsDebug"));
var preDay = -6; // 往前抓幾天!?
DownloadViewModel viewModel = new DownloadViewModel();
string packageFileName = string.Format("Shipcheck_{0}.zip", DateTime.Now.ToString("yyyyMMddHHmmssfff"));
var currentDate = DateTime.Now.ToString("yyyyMMdd hhmmss");
try
{
_logger.Debug("download start...");
using (BookingCarSSISEntities db = new BookingCarSSISEntities())
{
//createMapInit();
var startDate = DateTime.Today.AddDays(preDay);
var glassFibreCloth = db.v28caxrf_s2
.Where(x => string.IsNullOrEmpty(x.CheckAccount))
.Where(x => DbFunctions.TruncateTime(x.DateCreated) >= startDate)
.AsEnumerable()
.Select(x => new GlassFibreClothItem
{
ROWIdx = x.ROWIdx,
CUFN = x.CUFN,
ODNO = x.ODNO,
UNPKWGT = x.UNPKWGT.HasValue ? x.UNPKWGT.Value.ToString() : null,
PDID = x.PDID,
CLHRLNO = x.CLHRLNO,
L = x.L,
GRSWGT = x.GRSWGT.HasValue ? x.GRSWGT.Value.ToString() : null,
DateCreated = x.DateCreated.HasValue ? x.DateCreated.Value.ToString(Resource.StrDateTimeFormat) : null,
PrintTime = x.PrintTime.HasValue ? x.PrintTime.Value.ToString(Resource.StrDateTimeFormat) : null,
CheckAccount = x.CheckAccount,
CheckTime = x.CheckTime.HasValue ? x.CheckTime.Value.ToString(Resource.StrDateTimeFormat) : null,
OUTMDAT = x.OUTMDAT
})
.OrderBy(x => x.ROWIdx)
.ToList();
viewModel.GlassFibreCloth = glassFibreCloth;
var copperFoil = db.V28EFQ7J
.Where(x => string.IsNullOrEmpty(x.CheckAccount))
.Where(x => DbFunctions.TruncateTime(x.DateCreated) >= startDate)
.AsEnumerable()
.Select(x => new CopperFoilItem
{
ROWIdx = x.ROWIdx,
CUNO = x.CUNO,
OMNO = x.OMNO,
KD = x.KD,
SPEC = x.SPEC,
IKPCNO = x.IKPCNO,
L = x.L.ToString(),
NETWGT = x.NETWGT.HasValue ? x.NETWGT.Value : (int?)null,
DateCreated = x.DateCreated.HasValue ? x.DateCreated.Value.ToString(Resource.StrDateTimeFormat) : null,
PrintTime = x.PrintTime.HasValue ? x.PrintTime.Value.ToString(Resource.StrDateTimeFormat) : null,
CheckAccount = x.CheckAccount,
CheckTime = x.CheckTime.HasValue ? x.CheckTime.Value.ToString(Resource.StrDateTimeFormat) : null,
IKDAT = x.IKDAT
})
.OrderBy(x => x.ROWIdx)
.ToList();
viewModel.CopperFoil = copperFoil;
//產出SQLite
var tempSqliteFilePath = SQLiteUtils.GenerateSQLiteZip(viewModel);
if (isDebug)
{
response = Request.CreateResponse(HttpStatusCode.OK);
}
else
{
if (File.Exists(tempSqliteFilePath))
{
FileStream fileStream = new FileStream(tempSqliteFilePath, FileMode.Open, FileAccess.Read)
{
};
response = Request.CreateResponse(HttpStatusCode.OK);
response.Content = new StreamContent(fileStream);
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = packageFileName
};
}
else
{
response = Request.CreateResponse(HttpStatusCode.InternalServerError);
}
}
}
}
catch (Exception ex)
{
_logger.Error("發生錯誤:{0}", ex.Message);
response = Request.CreateResponse(HttpStatusCode.InternalServerError);
throw ex;
}
finally
{
_logger.Debug("download done...");
}
return(response);
}
/// <summary>
/// 指定SQLSERVER到SQLITE
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entities"></param>
/// <param name="tableName"></param>
/// <param name="createTableCommand"></param>
public static string GenerateSQLiteZip(DownloadViewModel viewModel)
{
var result = "";
var downloadFolder = System.Web.Hosting.HostingEnvironment.MapPath(CommonUtils.AppSettings("DownloadFolder"));//存放資料夾
var isDebug = bool.Parse(CommonUtils.AppSettings("IsDebug"));
var zipFileName = " ShipCheck.zip";
string targetPath = "";//sqlite存放路徑
SQLiteConnection con = null;
try
{
if (!isDebug)
{
//上線時,檔案會在存放資料夾產生流水資料夾 EX: Folder\Download\20140829154043_7f12d7b1-f3ca-4456-8527-0baa1d32ce67
var folderName = DateTime.Now.ToString("yyyyMMddHHmmss_") + Guid.NewGuid();
downloadFolder = Path.Combine(downloadFolder, folderName);//更新存放資料夾
if (!Directory.Exists(downloadFolder))
{
Directory.CreateDirectory(downloadFolder);
}
}
targetPath = Path.Combine(downloadFolder, "ShipCheck.db");
string dataSrouce = string.Format("Data Source={0}", targetPath);
//組出來源
if (!System.IO.File.Exists(targetPath))
{
SQLiteConnection.CreateFile(targetPath);
}
//組出輸出
using (con = new SQLiteConnection(dataSrouce))
{
using (SQLiteCommand com = new SQLiteCommand(con))
{
con.Open();
using (var trans = con.BeginTransaction())
{
//玻纖布
com.CommandText = viewModel.GlassFibreCloth.ToCreateTableScript("GlassFibreCloth");
com.ExecuteNonQuery();
var glassFibreClothCmds = viewModel.GlassFibreCloth.ToInsertScript("GlassFibreCloth");
foreach (var item in glassFibreClothCmds)
{
com.CommandText = item;
com.ExecuteNonQuery();
}
//銅箔
com.CommandText = viewModel.CopperFoil.ToCreateTableScript("CopperFoil");
com.ExecuteNonQuery();
// var inventory_index_script = @"
// CREATE INDEX IDX_WAREHOUSEID on Inventory(WarehouseID ASC);
// CREATE INDEX IDX_WAREHOUSEID_LOCATION on Inventory(WarehouseID ASC,Location ASC);
// CREATE INDEX IDX_WAREHOUSEID_STATUSCODE on Inventory(WarehouseID ASC,StatusCode ASC);
// ";
var copperFoilCmds = viewModel.CopperFoil.ToInsertScript("CopperFoil");
foreach (var item in copperFoilCmds)
{
com.CommandText = item;
com.ExecuteNonQuery();
}
trans.Commit();
};
}
}
//壓縮
if (!isDebug && File.Exists(targetPath))
{
var saveZipPath = Path.Combine(downloadFolder, zipFileName);
//壓縮
using (ZipFile zip = new ZipFile())
{
zip.AddFile(targetPath, "");
zip.Save(saveZipPath);
}
result = saveZipPath;
}
else
{
result = targetPath;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
con = null;
}
}
return(result);
}
