public ActionResult getModel(FormModel postedModel)
{
RootObject MODELFROMDATABASE = new RootObject();
PDFModel PDFmdoel = new PDFModel()
{
DuraionTime = postedModel.formfillingtime,
OperatorID = postedModel.phone,
hospitalName = GlobalVariables.name,
timestamp = postedModel.timestamp,
PDFTitle = postedModel.title,
catID = postedModel.catID,
};
if (GlobalVariables.address == "" || GlobalVariables.address == null)
{
string json = "";
using (var client = new WebClient())
{
json = client.DownloadString("http://www.supectco.com/webs/GDP/Admin/getHospitalDetail.php?active=true");
HospitalRoot log = JsonConvert.DeserializeObject <HospitalRoot>(json);
GlobalVariables.address = log.hospitalDetail.First().address;
GlobalVariables.name = log.hospitalDetail.First().name;
GlobalVariables.mohandes = log.hospitalDetail.First().mohandes;
GlobalVariables.ostan = log.hospitalDetail.First().ostan;
GlobalVariables.sharestan = log.hospitalDetail.First().sharestan;
GlobalVariables.phone = log.hospitalDetail.First().phone;
}
}
//if(GlobalVariables.modelList != "")
//{
//}
using (var client = new WebClient())
{
GlobalVariables.modelList = client.DownloadString("http://www.supectco.com/webs/GDP/Admin/getListOfFeaterForApp.php?CatID=1");
}
try
{
string json3 = "";
using (var client = new WebClient())
{
json3 = client.DownloadString("http://www.supectco.com/webs/GDP/Admin/getLastPDFName.php?name=" + postedModel.title);
}
if (json3.Length > 1)
{
List <string> firstList = json3.Split(',').ToList();
List <string> TagIds = firstList[0].Split('_').ToList();
if (TagIds.Count() > 1)
{
postedModel.title = postedModel.title + "_" + (int.Parse(TagIds[1]) + 1).ToString();
}
else
{
postedModel.title = postedModel.title + "_1";
}
PDFmdoel.referer = firstList[1];
PDFmdoel.PDFTitle = postedModel.title;
}
}
catch (Exception e)
{
System.IO.File.WriteAllText(e.Message, postedModel.title);
}
//string txtpath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sample.txt");
//System.IO.File.AppendAllText(txtpath, String.Empty);
System.IO.File.WriteAllText(txtpath, postedModel.title);
string id = "";
try
{
MODELFROMDATABASE = JsonConvert.DeserializeObject <RootObject>(GlobalVariables.modelList);
System.IO.File.AppendAllText(txtpath, " " + MODELFROMDATABASE.featurDataDetail.Count().ToString());
string srt = postedModel.model;
// string txtpath2 = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sample2.txt");
//System.IO.File.AppendAllText(txtpath, String.Empty);
//System.IO.File.AppendAllText(txtpath, srt);
List <model> mymodel = JsonConvert.DeserializeObject <List <model> >(srt);
string count = mymodel.Count().ToString();
System.IO.File.AppendAllText(txtpath, String.Empty);
System.IO.File.AppendAllText(txtpath, count);
var testFile = "";
testFile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PDF/pump.pdf");
string emptyNamePDF = "";
emptyNamePDF = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PDF/pumpEmpty.pdf");
//set pdf in database ;
//create pdf proccess
//Spire.Pdf.PdfDocument doc = new Spire.Pdf.PdfDocument();
//doc.LoadFromFile(testFile);
//PdfTextFind results = null;
////setting position for every title
//PdfPageBase page;
//foreach (var item in mymodel)
//{
// int PAGE = Convert.ToInt32(item.page) - 1;
// page = doc.Pages[PAGE];
// if (item.value != "master")
// {
// results = page.FindText(item.title).Finds.First();
// float width = results.Size.Width;
// item.x = (results.Position.X + width).ToString();
// item.y = (results.Position.Y).ToString();
// }
//}
var testFile2 = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), emptyNamePDF);
string newFile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ResultPDF/result" + postedModel.title + ".pdf");
string finalFile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ResultPDF/" + postedModel.title + ".pdf");
Spire.Pdf.PdfDocument doc2 = new Spire.Pdf.PdfDocument();
doc2.LoadFromFile(testFile2);
float pageheight = 0;
foreach (PdfPageBase spipage in doc2.Pages)
{
int index = doc2.Pages.IndexOf(spipage);
if (index == 0)
{
mymodel.Where(x => x.page == "1" && x.title == "address").FirstOrDefault().value = GlobalVariables.address;
mymodel.Where(x => x.page == "1" && x.title == "hospital name").FirstOrDefault().value = GlobalVariables.name;
mymodel.Where(x => x.page == "1" && x.title == "mohandes").FirstOrDefault().value = GlobalVariables.mohandes;
mymodel.Where(x => x.page == "1" && x.title == "ostan").FirstOrDefault().value = GlobalVariables.ostan;
mymodel.Where(x => x.page == "1" && x.title == "shahrestan").FirstOrDefault().value = GlobalVariables.sharestan;
mymodel.Where(x => x.page == "1" && x.title == "phone").FirstOrDefault().value = GlobalVariables.phone;
foreach (var item in mymodel.Where(x => x.page == "1" && x.title == "mark"))
{
PDFmdoel.mark = PDFmdoel.mark + item.value;
}
foreach (var item in mymodel.Where(x => x.page == "1" && x.title == "model"))
{
PDFmdoel.model = PDFmdoel.model + item.value;
}
foreach (var item in mymodel.Where(x => x.page == "1" && x.title == "esteghrar"))
{
PDFmdoel.position = PDFmdoel.position + item.value;
}
PDFmdoel.serial = mymodel.Where(x => x.page == "1" && x.title == "serial").FirstOrDefault().value;
PDFmdoel.amval = mymodel.Where(x => x.page == "1" && x.title == "amvaal").FirstOrDefault().value;
if (mymodel.Where(x => x.page == "1" && x.title == "Passed").FirstOrDefault() != null)
{
if (mymodel.Where(x => x.page == "1" && x.title == "Passed").FirstOrDefault().value == "true")
{
PDFmdoel.status = "Passed";
}
}
if (mymodel.Where(x => x.page == "1" && x.title == "Limited").FirstOrDefault() != null)
{
if (mymodel.Where(x => x.page == "1" && x.title == "Limited").FirstOrDefault().value == "true")
{
PDFmdoel.status = "Limited";
}
}
if (mymodel.Where(x => x.page == "1" && x.title == "Failed").FirstOrDefault() != null)
{
if (mymodel.Where(x => x.page == "1" && x.title == "Failed").FirstOrDefault().value == "true")
{
PDFmdoel.status = "Failed";
}
}
System.IO.File.WriteAllText(txtpath, "enter foreach");
foreach (var item in MODELFROMDATABASE.featurDataDetail.Where(x => x.referer != ""))
{
model chosen = mymodel.Where(x => x.title == item.title && x.page == item.page).SingleOrDefault();
System.IO.File.AppendAllText(txtpath, " " + item.title);
if (item.max == -1 && item.min != -1)
{
if (double.Parse(chosen.value) >= item.min)
{
mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Passed";
}
else
{
mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Failed";
}
}
else if (item.max != -1 && item.min == -1)
{
if (double.Parse(chosen.value) <= item.max)
{
mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Passed";
}
else
{
mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Failed";
}
}
else
{
if (double.Parse(chosen.value) >= item.min && double.Parse(chosen.value) <= item.max)
{
mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Passed";
}
else
{
mymodel.Where(x => x.title == item.referer).SingleOrDefault().value = "Failed";
}
}
}
string flow10 = mymodel.Where(x => x.title == "flow10").FirstOrDefault().value;
if (flow10 != "")
{
mymodel.Where(x => x.title == "error10").FirstOrDefault().value = Math.Round(((decimal.Parse(flow10) - 10) * 10), 2).ToString().Replace("-", "");
}
string flow50 = mymodel.Where(x => x.title == "flow50").FirstOrDefault().value;
if (flow50 != "")
{
mymodel.Where(x => x.title == "error50").FirstOrDefault().value = Math.Round(((decimal.Parse(flow50) - 50) * 2), 2).ToString().Replace("-", "");
}
string flow100 = mymodel.Where(x => x.title == "flow100").FirstOrDefault().value;
if (flow100 != "")
{
mymodel.Where(x => x.title == "error100").FirstOrDefault().value = Math.Round((decimal.Parse(flow100) - 100), 2).ToString().Replace("-", "");
}
//ایمنی
if (mymodel.Where(x => x.title == "resmonfas").FirstOrDefault().value == "Passed" && mymodel.Where(x => x.title == "class").FirstOrDefault().value != "CLASS II")
{
mymodel.Where(x => x.title == "monfasel").FirstOrDefault().value = Math.Round(RandomNumberBetween(0.5, 0.1), 1).ToString();
}
if (mymodel.Where(x => x.title == "resmotas").FirstOrDefault().value == "Passed" && mymodel.Where(x => x.title == "class").FirstOrDefault().value != "CLASS II")
{
mymodel.Where(x => x.title == "motasel").FirstOrDefault().value = Math.Round(RandomNumberBetween(1.6, 0.2), 1).ToString();
}
if (mymodel.Where(x => x.title == "class").FirstOrDefault().value != "CLASS II")
{
int adi = (int)RandomNumberBetween(4600, 4950);
int pluse = (int)RandomNumberBetween(23, 47);
int tak = (int)RandomNumberBetween(7200, 9700);
int takpluse = (int)RandomNumberBetween(180, 280);
if (mymodel.Where(x => x.title == "res12").FirstOrDefault().value == "Passed")
{
mymodel.Where(x => x.title == "nashti1").FirstOrDefault().value = ((int)adi).ToString();
mymodel.Where(x => x.title == "nashti2").FirstOrDefault().value = ((int)tak).ToString();
}
if (mymodel.Where(x => x.title == "res34").FirstOrDefault().value == "Passed")
{
mymodel.Where(x => x.title == "nashti3").FirstOrDefault().value = ((int)(adi + pluse)).ToString();
mymodel.Where(x => x.title == "nashti4").FirstOrDefault().value = ((int)(tak + takpluse)).ToString();
}
}
int nashtiBadiAdi = (int)RandomNumberBetween(57, 83);
int nashtiBadiAdiPluse = (int)RandomNumberBetween(1, 7);
int nashtiBaditak = (int)RandomNumberBetween(280, 470);
int nashtiBaditakPluse = (int)RandomNumberBetween(10, 15);
int badane2 = (int)nashtiBaditak;
int badane3 = badane2 + 3;
int badane5 = (int)nashtiBaditak + (int)nashtiBaditakPluse;
int badane6 = badane5 + 3;
if (mymodel.Where(x => x.title == "res123" && x.page == "4").FirstOrDefault().value == "Passed")
{
mymodel.Where(x => x.title == "badane1").FirstOrDefault().value = ((int)nashtiBadiAdi + (int)nashtiBadiAdiPluse).ToString();
mymodel.Where(x => x.title == "badane2").FirstOrDefault().value = badane2.ToString();
mymodel.Where(x => x.title == "badane3").FirstOrDefault().value = badane3.ToString();
}
if (mymodel.Where(x => x.title == "res456" && x.page == "4").FirstOrDefault().value == "Passed")
{
mymodel.Where(x => x.title == "badane4").FirstOrDefault().value = ((int)nashtiBadiAdi + (2 * (int)nashtiBadiAdiPluse)).ToString();
mymodel.Where(x => x.title == "badane5").FirstOrDefault().value = badane5.ToString();
mymodel.Where(x => x.title == "badane6").FirstOrDefault().value = badane6.ToString();
}
if (mymodel.Where(x => x.title == "res7" && x.page == "4").FirstOrDefault().value == "Passed")
{
mymodel.Where(x => x.title == "badane7").FirstOrDefault().value = ((int)nashtiBadiAdi).ToString();
}
int nashtiBadiAdi2 = (int)RandomNumberBetween(57, 83);
int nashtiBadiAdiPluse2 = (int)RandomNumberBetween(1, 7);
int nashtiBaditak2 = (int)RandomNumberBetween(280, 470);
int nashtiBaditakPluse2 = (int)RandomNumberBetween(10, 15);
int ptp2 = (int)nashtiBaditak2;
int ptp3 = ptp2 + 3;
int ptp5 = (int)nashtiBaditak2 + (int)nashtiBaditakPluse2;
int ptp6 = ptp5 + 3;
if (mymodel.Where(x => x.title == "res123" && x.page == "5").FirstOrDefault().value == "Passed")
{
mymodel.Where(x => x.title == "ptp1").FirstOrDefault().value = ((int)nashtiBadiAdi2 + nashtiBadiAdiPluse2).ToString();
mymodel.Where(x => x.title == "ptp2").FirstOrDefault().value = ptp2.ToString();
mymodel.Where(x => x.title == "ptp3").FirstOrDefault().value = ptp3.ToString();
}
if (mymodel.Where(x => x.title == "res456" && x.page == "5").FirstOrDefault().value == "Passed")
{
mymodel.Where(x => x.title == "ptp4").FirstOrDefault().value = ((int)nashtiBadiAdi2 + (2 * nashtiBadiAdiPluse2)).ToString();
mymodel.Where(x => x.title == "ptp5").FirstOrDefault().value = ptp5.ToString();
mymodel.Where(x => x.title == "ptp6").FirstOrDefault().value = ptp6.ToString();
}
if (mymodel.Where(x => x.title == "res7" && x.page == "5").FirstOrDefault().value == "Passed")
{
mymodel.Where(x => x.title == "ptp7").FirstOrDefault().value = ((int)nashtiBadiAdi2).ToString();
}
if (mymodel.Where(x => x.title == "class").FirstOrDefault().value == "CLASS II")
{
mymodel.Where(x => x.title == "monfasel").FirstOrDefault().value = "----";
mymodel.Where(x => x.title == "motasel").FirstOrDefault().value = "----";
mymodel.Where(x => x.title == "nashti1").FirstOrDefault().value = "----";
mymodel.Where(x => x.title == "nashti2").FirstOrDefault().value = "----";
mymodel.Where(x => x.title == "nashti3").FirstOrDefault().value = "----";
mymodel.Where(x => x.title == "nashti4").FirstOrDefault().value = "----";
mymodel.Where(x => x.title == "resmonfas").FirstOrDefault().value = "Not Checked";
mymodel.Where(x => x.title == "resmotas").FirstOrDefault().value = "Not Checked";
mymodel.Where(x => x.title == "res12").FirstOrDefault().value = "Not Checked";
mymodel.Where(x => x.title == "res34").FirstOrDefault().value = "Not Checked";
}
else
{
if (mymodel.Where(x => x.title == "kablbargh").FirstOrDefault().value == "غیر قابل انفصال")
{
mymodel.Where(x => x.title == "monfasel").FirstOrDefault().value = "----";
mymodel.Where(x => x.title == "resmonfas").FirstOrDefault().value = "Not Checked";
}
else
{
mymodel.Where(x => x.title == "motasel").FirstOrDefault().value = "----";
mymodel.Where(x => x.title == "resmotas").FirstOrDefault().value = "Not Checked";
}
}
}
List <model> pageFieldes = mymodel.Where(x => x.page == (index + 1).ToString()).ToList();
pageheight = spipage.Size.Height;
foreach (var item in pageFieldes)
{
PdfTrueTypeFont Arial = new PdfTrueTypeFont(new System.Drawing.Font("Arial", 10f), true);
PdfTrueTypeFont Arial9 = new PdfTrueTypeFont(new System.Drawing.Font("Arial", 9f), true);
Spire.Pdf.Graphics.PdfFont font1 = new Spire.Pdf.Graphics.PdfFont(PdfFontFamily.TimesRoman, 9);
Spire.Pdf.Graphics.PdfBrush brush = PdfBrushes.Black;
PdfStringFormat format = new PdfStringFormat();
SizeF size = Arial9.MeasureString(item.value, format);
float Xposition = float.Parse(item.x) - size.Width;
float yposition = float.Parse(item.y) - 1;
PointF position = new PointF(Xposition, float.Parse(item.y));
if (item.type == 1)
{
if (item.value == "true")
{
string imagepath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "checked.png");
Spire.Pdf.Graphics.PdfImage image = Spire.Pdf.Graphics.PdfImage.FromFile(imagepath);
float width = image.Width * 0.75f;
float height = image.Height * 0.75f;
spipage.Canvas.DrawImage(image, Xposition, yposition, 10, 10);
}
else
{
string imagepath2 = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "unchecked.png");
Spire.Pdf.Graphics.PdfImage image2 = Spire.Pdf.Graphics.PdfImage.FromFile(imagepath2);
spipage.Canvas.DrawImage(image2, Xposition, yposition, 10, 10);
}
}
else
{
if (item.font == "E")
{
spipage.Canvas.DrawString(item.value, Arial, brush, position);
}
else
{
position.X = position.X;
spipage.Canvas.DrawString(item.value, Arial, brush, position, new PdfStringFormat()
{
RightToLeft = true
});
}
}
}
;
}
doc2.SaveToFile(newFile, FileFormat.PDF);
doc2.Close();
string srt2 = "";
using (PdfReader pdfReader = new PdfReader(newFile))
{
float widthTo_Trim = pageheight - 15;// iTextSharp.text.Utilities.MillimetersToPoints(450);
using (FileStream output = new FileStream(finalFile, FileMode.Create, FileAccess.Write))
{
using (PdfStamper pdfStamper = new PdfStamper(pdfReader, output))
{
iTextSharp.text.Rectangle cropBox = pdfReader.GetCropBox(1);
cropBox.Top = widthTo_Trim;
pdfReader.GetPageN(1).Put(PdfName.CROPBOX, new PdfRectangle(cropBox));
//for (int page = 1; page <= pdfReader.NumberOfPages; page++)
//{
//}
}
PDFmdoel.PDFPath = finalFile;
returnModel m2 = new returnModel()
{
status = 200,
message = "http://gdp.sup-ect.ir/ResultPDF/" + postedModel.title + ".pdf",
};
srt2 = JsonConvert.SerializeObject(m2);
}
pdfReader.Close();
setpdfindatabase(PDFmdoel);
}
return(Content(srt2));
}
catch (Exception e)
{
string txtpath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sample.txt");
System.IO.File.AppendAllText(txtpath, e.Message);
returnModel m = new returnModel()
{
status = 400,
message = e.Message + id,
};
string srt = JsonConvert.SerializeObject(m);
return(Content(srt));
}
}
static void Main(string[] args)
{
var conn = ConfigurationManager.AppSettings["DBConnectionString"].ToString();
string sourceDirectory;
List <returnModel> returnCollection = new List <returnModel>();
string fileName = "";
int counterFile = 1;
int counterLine;
int counterFileValidate = 1;
#region Fancy header
/*
* Console.Write(FiggleFonts.Ogre.Render("------------"));
* List<char> chars = new List<char>()
* {
* ' ', 'C', 'r', 'e', 'a', 't', 'e', 'd', ' ',
* 'b', 'y', ' ',
* 'P', 'i', 'r', 'i', 'y', 'a', 'V', ' '
* };
* Console.Write("---------------", Color.LawnGreen);
* Console.WriteWithGradient(chars, Color.Blue, Color.Purple, 16);
* Console.Write("---------------", Color.LawnGreen);
* Console.WriteLine("\n");
*/
#endregion
// Ask the user to type path
if (args.Length == 0)
{
// Display title
Console.Title = "ExcelToDB 1.0.1";
// Display header
Console.WriteWithGradient(FiggleFonts.Banner.Render("excel to db"), Color.LightGreen, Color.ForestGreen, 16);
Console.ReplaceAllColorsWithDefaults();
// Display copyright
Console.WriteLine(" ---------------------- Created by PiriyaV -----------------------\n", Color.LawnGreen);
Console.Write(@"Enter source path (eg: D:\folder) : ", Color.LightYellow);
sourceDirectory = Convert.ToString(Console.ReadLine());
Console.Write("\n");
}
else
{
sourceDirectory = Convert.ToString(args[0]);
}
// Variable for backup
string folderBackup = "imported_" + DateTime.Now.ToString("ddMMyyyy_HHmmss");
string folderBackupPath = Path.Combine(sourceDirectory, folderBackup);
// Initial values
int LineNum;
int ColumnNum;
string sheetName = "Sheet1";
string TableName = "[SAPMM-WM]";
int i = 0;
int ColumnNumChecker = 53;
bool sheetChecker = true;
try
{
// Full path for txt
var FilePath = Directory.EnumerateFiles(sourceDirectory, "*.*", SearchOption.TopDirectoryOnly).Where(s => s.ToLower().EndsWith(".xls") || s.ToLower().EndsWith(".xlsx"));
// Count txt file
DirectoryInfo di = new DirectoryInfo(sourceDirectory);
int FileNumXls = di.GetFiles("*.xls").Length;
int FileNumXlsx = di.GetFiles("*.xlsx").Length;
int FileNum = FileNumXls + FileNumXlsx;
// Throw no txt file
if (FileNum == 0)
{
throw new ArgumentException("Excel file not found in folder.");
}
#region Validate Section
var pbValidate = new ProgressBar(PbStyle.DoubleLine, FileNum);
foreach (string currentFile in FilePath)
{
sheetChecker = true;
// Update progress bar (Overall)
fileName = Path.GetFileName(currentFile);
pbValidate.Refresh(counterFileValidate, "Validating, Please wait...");
Thread.Sleep(50);
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
using (var stream = File.Open(currentFile, FileMode.Open, FileAccess.Read))
{
// ExcelDataReader Config
var conf = new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
};
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// Validation Excel file
do
{
if (reader.Name == sheetName)
{
sheetChecker = false;
while (reader.Read())
{
if (i > 0)
{
int rowNO = i + 1;
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 1, 10, "B", "Material Type");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 2, 255, "C", "Material Type Description");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 3, 10, "D", "Material Group");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 4, 255, "E", "Matl Grp Desc#");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 5, 12, "F", "Material");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 6, 255, "G", "Description");
ValidateDate(reader, pbValidate, rowNO, counterFileValidate, 7, "H", "Posting Date");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 8, 255, "I", "ได้รับมาจาก");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 9, 255, "J", "จ่ายไปให้");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 10, 10, "K", "Movement type");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 11, 255, "L", "Mvt Type Text");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 12, 100, "M", "Batch");
ValidateDate(reader, pbValidate, rowNO, counterFileValidate, 13, "N", "MFG Date");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 14, 100, "O", "Manufacturer Batch");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 15, 100, "P", "Manufacturer");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 16, 255, "Q", "Manufacturer Name");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 17, 100, "R", "Vendor");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 18, 255, "S", "Vendor Name");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 19, 20, "T", "Sold-to");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 20, 255, "U", "Sold-to Name");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 21, 255, "V", "Sold-to Address");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 22, 100, "W", "Sold-to Province");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 23, 20, "X", "Ship-to");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 24, 255, "Y", "Ship-to Name");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 25, 255, "Z", "Ship-to Address");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 26, 100, "AA", "Ship-to Province");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 27, 5, "AB", "Customer Group 1");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 28, 100, "AC", "Customer Group 1 - Desc#");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 29, 5, "AD", "Customer Group 2");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 30, 100, "AE", "Customer Group 2 - Desc#");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 31, 5, "AF", "Customer Group 3");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 32, 100, "AG", "Customer Group 3 - Desc#");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 33, 20, "AH", "FG material");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 34, 255, "AI", "FG Material Description");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 35, 100, "AJ", "FG Batch");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 36, 5, "AK", "Cost Center");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 37, 255, "AL", "Cost Center Description");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 38, 10, "AM", "Plant");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 39, 10, "AN", "Storage Loc#");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 40, 10, "AO", "Dest# Plant");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 41, 10, "AP", "Dest# Sloc");
ValidateFloat(reader, pbValidate, rowNO, counterFileValidate, 42, "AQ", "ยอดยกมา");
ValidateFloat(reader, pbValidate, rowNO, counterFileValidate, 43, "AR", "ปริมาณรับ");
ValidateFloat(reader, pbValidate, rowNO, counterFileValidate, 44, "AS", "ปริมาณจ่าย");
ValidateFloat(reader, pbValidate, rowNO, counterFileValidate, 45, "AT", "ปริมาณคงเหลือ");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 46, 20, "AU", "Unit");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 47, 255, "AV", "หมายเหตุ");
ValidateDate(reader, pbValidate, rowNO, counterFileValidate, 48, "AW", "Entered on");
ValidateDate(reader, pbValidate, rowNO, counterFileValidate, 49, "AX", "Entered at");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 50, 20, "AY", "Material Doc#");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 51, 10, "BZ", "Mat# Doc# Year");
ValidateString(reader, pbValidate, rowNO, counterFileValidate, 52, 5, "BA", "Mat# Doc#Item");
}
}
}
} while (reader.NextResult());
}
}
// Change wording in progress bar
if (counterFileValidate == FileNum)
{
pbValidate.Refresh(counterFileValidate, "Validate finished.");
}
// Return error if excel file don't have specific sheet name
if (sheetChecker)
{
pbValidate.Refresh(counterFileValidate, "Validate failed.");
throw new ArgumentException($"Excel must have sheet name \" {sheetName} \"");
}
counterFileValidate++;
}
#endregion
#region Import Section
// Create progress bar (Overall)
var pbOverall = new ProgressBar(PbStyle.DoubleLine, FileNum);
foreach (string currentFile in FilePath)
{
// Initial variable
LineNum = 0;
ColumnNum = 0;
counterLine = 1;
returnModel Model = new returnModel();
fileName = Path.GetFileName(currentFile);
// Update progress bar (Overall)
pbOverall.Refresh(counterFile, "Importing, Please wait...");
Thread.Sleep(50);
using (var stream = File.Open(currentFile, FileMode.Open, FileAccess.Read))
{
// ExcelDataReader Config
var conf = new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
};
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// Validation Excel file
do
{
if (reader.Name == sheetName)
{
// Read as DataSet
var result = reader.AsDataSet(conf);
// Convert to Datatable
DataTable dt = result.Tables[sheetName];
// Row count
LineNum = dt.Rows.Count;
// Column count
ColumnNum = dt.Columns.Count;
// Validate excel column
if (ColumnNum != ColumnNumChecker)
{
pbOverall.Refresh(counterFile, "Import error occured");
throw new ArgumentException($"Excel file must have {ColumnNumChecker} column!");
}
// Sanitize data
foreach (DataColumn c in dt.Columns)
{
if (c.DataType == typeof(string))
{
foreach (DataRow r in dt.Rows)
{
r[c.ColumnName] = r[c.ColumnName].ToString().Trim();
// Convert empty string into NULL
if (r[c.ColumnName].ToString().Length == 0)
{
r[c.ColumnName] = DBNull.Value;
}
}
}
}
using (SqlBulkCopy bc = new SqlBulkCopy(conn, SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.TableLock))
{
bc.DestinationTableName = TableName;
bc.BatchSize = reader.RowCount;
bc.ColumnMappings.Add(1, "[Material Type]");
bc.ColumnMappings.Add(2, "[Material Type Description]");
bc.ColumnMappings.Add(3, "[Material Group]");
bc.ColumnMappings.Add(4, "[Matl Grp Desc#]");
bc.ColumnMappings.Add(5, "[Material]");
bc.ColumnMappings.Add(6, "[Description]");
bc.ColumnMappings.Add(7, "[Posting Date]");
bc.ColumnMappings.Add(8, "[ได้รับมาจาก]");
bc.ColumnMappings.Add(9, "[จ่ายไปให้]");
bc.ColumnMappings.Add(10, "[Movement type]");
bc.ColumnMappings.Add(11, "[Mvt Type Text]");
bc.ColumnMappings.Add(12, "[Batch]");
bc.ColumnMappings.Add(13, "[MFG Date]");
bc.ColumnMappings.Add(14, "[Manufacturer Batch]");
bc.ColumnMappings.Add(15, "[Manufacturer]");
bc.ColumnMappings.Add(16, "[Manufacturer Name]");
bc.ColumnMappings.Add(17, "[Vendor]");
bc.ColumnMappings.Add(18, "[Vendor Name]");
bc.ColumnMappings.Add(19, "[Sold-to]");
bc.ColumnMappings.Add(20, "[Sold-to Name]");
bc.ColumnMappings.Add(21, "[Sold-to Address]");
bc.ColumnMappings.Add(22, "[Sold-to Province]");
bc.ColumnMappings.Add(23, "[Ship-to]");
bc.ColumnMappings.Add(24, "[Ship-to Name]");
bc.ColumnMappings.Add(25, "[Ship-to Address]");
bc.ColumnMappings.Add(26, "[Ship-to Province]");
bc.ColumnMappings.Add(27, "[Customer Group 1]");
bc.ColumnMappings.Add(28, "[Customer Group 1 - Desc#]");
bc.ColumnMappings.Add(29, "[Customer Group 2]");
bc.ColumnMappings.Add(30, "[Customer Group 2 - Desc#]");
bc.ColumnMappings.Add(31, "[Customer Group 3]");
bc.ColumnMappings.Add(32, "[Customer Group 3 - Desc#]");
bc.ColumnMappings.Add(33, "[FG material]");
bc.ColumnMappings.Add(34, "[FG Material Description]");
bc.ColumnMappings.Add(35, "[FG Batch]");
bc.ColumnMappings.Add(36, "[Cost Center]");
bc.ColumnMappings.Add(37, "[Cost Center Description]");
bc.ColumnMappings.Add(38, "[Plant]");
bc.ColumnMappings.Add(39, "[Storage Loc#]");
bc.ColumnMappings.Add(40, "[Dest# Plant]");
bc.ColumnMappings.Add(41, "[Dest# Sloc]");
bc.ColumnMappings.Add(42, "[ยอดยกมา]");
bc.ColumnMappings.Add(43, "[ปริมาณรับ]");
bc.ColumnMappings.Add(44, "[ปริมาณจ่าย]");
bc.ColumnMappings.Add(45, "[ปริมาณคงเหลือ]");
bc.ColumnMappings.Add(46, "[Unit]");
bc.ColumnMappings.Add(47, "[หมายเหตุ]");
bc.ColumnMappings.Add(48, "[Entered on]");
bc.ColumnMappings.Add(49, "[Entered at]");
bc.ColumnMappings.Add(50, "[Material Doc#]");
bc.ColumnMappings.Add(51, "[Mat# Doc# Year]");
bc.ColumnMappings.Add(52, "[Mat# Doc#Item]");
bc.WriteToServer(dt);
}
}
} while (reader.NextResult());
counterLine++;
}
// Create folder for file import successful
if (!Directory.Exists(folderBackupPath))
{
Directory.CreateDirectory(folderBackupPath);
}
// Move file to folder backup
string destFile = Path.Combine(folderBackupPath, fileName);
File.Move(currentFile, destFile);
// Add detail to model for showing in table
Model.RowNo = LineNum;
Model.FileName = fileName;
returnCollection.Add(Model);
// Change wording in progress bar
if (counterFile == FileNum)
{
pbOverall.Refresh(counterFile, "Import finished.");
}
counterFile++;
}
#endregion
}
}
catch (Exception ex)
{
//pbOverall.Refresh(counterFile, "Import failed");
// Show error message
Console.Write("\nError occured : ", Color.OrangeRed);
Console.WriteLine(ex.Message);
//Console.WriteLine("Error trace : " + ex.StackTrace);
// Show error on
if (!String.IsNullOrEmpty(fileName))
{
Console.Write("\nError on : ", Color.OrangeRed);
Console.WriteLine("'" + fileName + "'");
}
// Show description
Console.WriteLine("\nPlease check your path or file and try again.\n", Color.Yellow);
}
finally
{
// Show table
if (returnCollection.Count > 0)
{
Console.WriteLine("\n--------------- Imported detail ---------------", Color.LightGreen);
ConsoleTable.From(returnCollection).Write();
}
//Console.WriteLine(JsonSerializer.Serialize(returnCollection));
// Show backup folder path
if (Directory.Exists(folderBackupPath))
{
Console.Write("\nImported folder : ", Color.LightGreen);
Console.WriteLine($"'{ folderBackupPath }'");
}
}
// Wait key to terminate
Console.Write("\nPress any key to close this window ");
Console.ReadKey();
}
