public void parseLine(string line, Dictionary <string, LineParser> parsers)
{
sbWord.Clear();
sbType.Clear();
sbAbbr.Clear();
string[] split = line.Split('\t');
try
{
if (split.Length != 3)
{
throw new ImportParsingException("Invalid line: " + line);
}
LineParser parser;
parsers.TryGetValue(split[2], out parser);
if (parser != null)
{
parser.parseLine(split[0]);
}
}
catch (ImportParsingException ipe)
{
ImportErrors.Add(ipe);
}
catch (Exception e)
{
ImportErrors.Add(new ImportParsingException(e.Message));
}
}
public ActionResult Continue(int id)//Document Type Id
{
string DataTableSessionVarName = "";
DataTableSessionVarName = User.Identity.Name.ToString() + "ImportData" + id.ToString();
DataTable dataTable = (DataTable)Session[DataTableSessionVarName];
string ErrorText = "";
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".ProcImportFinishingInvoiceReturnFromTextFile"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@TextFileData", dataTable);
cmd.Parameters.AddWithValue("@SkipValidation", 1);
cmd.CommandTimeout = 1000;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
}
}
List <ImportErrors> ImportErrorList = new List <ImportErrors>();
if (ds.Tables[0].Rows.Count == 0)
{
ViewBag.id = id;
return(View("Sucess"));
}
else
{
for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
{
if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
{
ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
}
ImportErrors ImportError = new ImportErrors();
ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
ImportErrorList.Add(ImportError);
}
if (ErrorText != "")
{
ViewBag.Error = ErrorText;
ViewBag.id = id;
return(View("Error", ImportErrorList));
}
return(View("Sucess"));
}
}
/// <summary>
/// Import2s the specified CSV RDR.
/// </summary>
/// <param name="csvRdr">The CSV RDR.</param>
public void Import2(Microsoft.VisualBasic.FileIO.TextFieldParser csvRdr)
{
//CurrentStatesBeingManaged = new List<State>(HospitalRegion.Default.SelectedStates);
var startTime = DateTime.Now;
Importing(this, EventArgs.Empty);
CountInserted = 0;
NumberOfErrors = 0;
AssertErrorFile();
csvRdr.ReadLine();
while (!csvRdr.EndOfData)
{
int n = 1;
IList <ImportError> errors = new List <ImportError>();
Events.GetEvent <PleaseStandByMessageUpdateEvent>()
.Publish("Importing line " + n.ToString());
try
{
ExtractAndSave2(csvRdr, errors);
ImportErrors.AddRange(errors);
}
finally
{
n++;
}
}
FinalProcessing();
Events.GetEvent <PleaseStandByMessageUpdateEvent>().Publish("Finalizing import...");
// what is the purpose of this delay???
const int MAX_DELAY_SECONDS = 3;
var elapsed = DateTime.Now - startTime;
var seconds = elapsed.TotalSeconds;
var remaining = MAX_DELAY_SECONDS - seconds;
if (remaining > 0)
{
Thread.Sleep(TimeSpan.FromSeconds(remaining));
}
Imported(this, EventArgs.Empty);
Events.GetEvent <PleaseStandByMessageUpdateEvent>().Publish("Import Complete");
Events.GetEvent <SimpleImportCompletedEvent>().Publish(this);
}
public async Task ParseAsync_ReturnsCorrectResults()
{
// Arrange
var dataWithoutHeader = new List <string[]>
{
new[] { "Mark", "23" },
new[] { "Thomas", "27" },
};
var testEntityMeta = _provider.GetMetadata(1, typeof(TestEntity), null, null);
var nameMeta = testEntityMeta.Property(nameof(TestEntity.Name));
var ageMeta = testEntityMeta.Property(nameof(TestEntity.Age));
var nameMapping = new PropertyMappingInfo(nameMeta, nameMeta)
{
Index = 0
};
var ageMapping = new PropertyMappingInfo(ageMeta, ageMeta)
{
Index = 1
};
var simpleProps = new List <PropertyMappingInfo> {
nameMapping, ageMapping
};
var collectionProps = new List <MappingInfo> {
};
var mapping = new MappingInfo(testEntityMeta, testEntityMeta, simpleProps, collectionProps, null, null);
var errors = new ImportErrors();
// Act
var entities = await _parser.ParseAsync <TestEntity>(dataWithoutHeader, mapping, errors);
// Assert
Assert.Collection(entities,
entity =>
{
Assert.Equal("Mark", entity.Name);
Assert.Equal(23, entity.Age);
},
entity =>
{
Assert.Equal("Thomas", entity.Name);
Assert.Equal(27, entity.Age);
}
);
}
public ActionResult SaleOrderExcelImport(int id)
{
int i = 0;
string[] StrArr = new string[] {};
string ErrorText = "";
//string WarningText = "";
if (Request.Files.Count == 0 || Request.Files[0].FileName == "")
{
ViewBag.id = id;
ModelState.AddModelError("", "Please select file.");
return(View("Index"));
}
var file = Request.Files[0];
string filePath = Request.MapPath(ConfigurationManager.AppSettings["ExcelFilePath"] + file.FileName);
file.SaveAs(filePath);
var excel = new ExcelQueryFactory();
excel.FileName = filePath;
var SaleOrderRecordList = from c in excel.Worksheet <SaleOrderExcel>() select c;
DataTable dataTable = new DataTable();
dataTable.Columns.Add("DocTypeId");
dataTable.Columns.Add("DocDate");
dataTable.Columns["DocDate"].DataType = System.Type.GetType("System.DateTime");
dataTable.Columns.Add("DocNo");
dataTable.Columns.Add("DivisionId");
dataTable.Columns.Add("SiteId");
dataTable.Columns.Add("DueDate");
dataTable.Columns["DueDate"].DataType = System.Type.GetType("System.DateTime");
dataTable.Columns.Add("SaleToBuyerName");
dataTable.Columns.Add("BuyerUpcCode");
dataTable.Columns.Add("CreatedBy");
dataTable.Columns.Add("ProductName");
dataTable.Columns.Add("Qty");
dataTable.Columns.Add("Rate");
dataTable.Columns.Add("Sr");
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
foreach (SaleOrderExcel item in SaleOrderRecordList)
{
i = i + 1;
var dr = dataTable.NewRow();
dr["DocTypeId"] = id;
dr["DocDate"] = DateTime.Now.Date;
dr["DocNo"] = item.BuyerOrderNo;
dr["DivisionId"] = (int)System.Web.HttpContext.Current.Session["DivisionId"];
dr["SiteId"] = (int)System.Web.HttpContext.Current.Session["SiteId"];
dr["DocDate"] = item.DueDate;
dr["SaleToBuyerName"] = item.SaleToBuyer;
dr["BuyerUpcCode"] = item.BuyerUpcCode;
dr["CreatedBy"] = User.Identity.Name;
dr["ProductName"] = item.Product;
dr["Qty"] = item.Quantity;
dr["Rate"] = item.Rate;
dr["Sr"] = i;
dataTable.Rows.Add(dr);
}
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".ProcImportSaleOrderFromExcelFile"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@TextFileData", dataTable);
cmd.CommandTimeout = 1000;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
}
}
List <ImportErrors> ImportErrorList = new List <ImportErrors>();
if (ds.Tables[0].Rows.Count == 0)
{
ViewBag.id = id;
return(View("Sucess"));
}
else
{
for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
{
if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
{
ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
}
ImportErrors ImportError = new ImportErrors();
ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
ImportErrorList.Add(ImportError);
}
if (ErrorText != "")
{
ViewBag.Error = ErrorText;
ViewBag.id = id;
string DataTableSessionVarName = "";
DataTableSessionVarName = User.Identity.Name.ToString() + "ImportData" + id.ToString();
Session[DataTableSessionVarName] = dataTable;
return(View("Error", ImportErrorList));
}
return(View("Sucess"));
}
}
public ActionResult PackingBarcodeImport(int?PackingHeaderId)
{
string ErrorText = "";
if (PackingHeaderId == 0 || PackingHeaderId == null)
{
ModelState.AddModelError("", "Please select Packing No.");
return(View("Index"));
}
var file = Request.Files[0];
string filePath = Request.MapPath(ConfigurationManager.AppSettings["ExcelFilePath"] + file.FileName);
file.SaveAs(filePath);
var excel = new ExcelQueryFactory();
excel.FileName = filePath;
var PackingBarcodeList = from c in excel.Worksheet <PackingBarcode>() select c;
DataTable dataTable = new DataTable();
dataTable.Columns.Add("BarCode");
dataTable.Columns.Add("PackingHeaderId");
dataTable.Columns.Add("CreatedBy");
dataTable.Columns.Add("Sr");
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
int i = 0;
foreach (var temp in PackingBarcodeList)
{
i++;
var dr = dataTable.NewRow();
dr["BarCode"] = temp.BarCode;
dr["PackingHeaderId"] = PackingHeaderId;
dr["CreatedBy"] = User.Identity.Name;
dr["Sr"] = i;
dataTable.Rows.Add(dr);
}
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".ProcImportPackingBarcode"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@ExcelFileData", dataTable);
cmd.CommandTimeout = 1000;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
}
}
List <ImportErrors> ImportErrorList = new List <ImportErrors>();
if (ds.Tables[0].Rows.Count == 0)
{
return(View("Sucess"));
}
else
{
for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
{
if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
{
ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
}
ImportErrors ImportError = new ImportErrors();
ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
ImportError.BarCodes = ds.Tables[0].Rows[j]["BarCodes"].ToString();
ImportErrorList.Add(ImportError);
}
if (ErrorText != "")
{
ViewBag.Error = ErrorText;// +WarningText;
string DataTableSessionVarName = "";
DataTableSessionVarName = User.Identity.Name.ToString() + "ImportDataPacking";
Session[DataTableSessionVarName] = dataTable;
return(View("Error", ImportErrorList));
}
return(View("Sucess"));
}
}
public ActionResult FinishingOrderCancelTextImport(int id, int?GodownId)
{
string[] StrArr = new string[] {};
string ErrorText = "";
//string WarningText = "";
if (Request.Files.Count == 0 || Request.Files[0].FileName == "")
{
ViewBag.id = id;
ModelState.AddModelError("", "Please select file.");
return(View("Index"));
}
if (GodownId == 0 || GodownId == null)
{
ViewBag.id = id;
ModelState.AddModelError("", "Please select Godown.");
return(View("Index"));
}
var file = Request.Files[0];
string filePath = Request.MapPath(ConfigurationManager.AppSettings["ExcelFilePath"] + file.FileName);
file.SaveAs(filePath);
StreamReader Sr;
Sr = new StreamReader(filePath);
string Line = "";
DataTable dataTable = new DataTable();
dataTable.Columns.Add("DocTypeId");
dataTable.Columns.Add("DocDate");
dataTable.Columns["DocDate"].DataType = System.Type.GetType("System.DateTime");
dataTable.Columns.Add("DocNo");
dataTable.Columns.Add("DivisionId");
dataTable.Columns.Add("SiteId");
dataTable.Columns.Add("ProcessCode");
dataTable.Columns.Add("OrderByCode");
dataTable.Columns.Add("ProductUidName");
dataTable.Columns.Add("SupplierCode");
dataTable.Columns.Add("CreatedBy");
dataTable.Columns.Add("GodownId");
var JobOrderCancelSettings = new JobOrderSettingsService(_unitOfWork).GetJobOrderSettingsForDocument(id, (int)System.Web.HttpContext.Current.Session["DivisionId"], (int)System.Web.HttpContext.Current.Session["SiteId"]);
string PrevSupplierCode = "";
string PrevDocNo = "";
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
string StrQry = " Declare @TmpTable as Table " +
" ( " +
" ProcessCode nVarchar(10), " +
" ProductUidName nVarchar(10), " +
" SupplierCode nVarchar(10), " +
" OrderByCode nVarchar(10) " +
" ) ";
int i = 0;
do
{
i++;
Line = Sr.ReadLine();
if (Line != null)
{
StrArr = Line.Split(new Char[] { ',' });
StrQry += " Insert Into @TmpTable (ProcessCode, ProductUidName, SupplierCode, OrderByCode) ";
StrQry += " Values ('" + StrArr[7] + "', '" + StrArr[13] + "', '" + StrArr[9] + "', '" + StrArr[11] + "')";
}
} while (Line != null);
Sr.Close();
string mQry = "";
mQry = StrQry + " Select ProcessCode, ProductUidName, SupplierCode, OrderByCode " +
" From @TmpTable " +
" Order by SupplierCode ";
DataSet TextData = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(mQry))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(TextData);
}
}
}
for (i = 0; i <= TextData.Tables[0].Rows.Count - 1; i++)
{
var dr = dataTable.NewRow();
dr["DocTypeId"] = id;
dr["DocDate"] = DateTime.Now.Date;
dr["DivisionId"] = (int)System.Web.HttpContext.Current.Session["DivisionId"];
dr["SiteId"] = (int)System.Web.HttpContext.Current.Session["SiteId"];
dr["ProductUidName"] = TextData.Tables[0].Rows[i]["ProductUidName"];
dr["SupplierCode"] = TextData.Tables[0].Rows[i]["SupplierCode"];
dr["CreatedBy"] = User.Identity.Name;
dr["ProcessCode"] = TextData.Tables[0].Rows[i]["ProcessCode"];
dr["OrderByCode"] = TextData.Tables[0].Rows[i]["OrderByCode"];
dr["GodownId"] = GodownId;
if (PrevSupplierCode != dr["SupplierCode"].ToString())
{
if (PrevDocNo == "")
{
dr["DocNo"] = new DocumentTypeService(_unitOfWork).FGetNewDocNo("DocNo", ConfigurationManager.AppSettings["DataBaseSchema"] + ".JobOrderCancelHeaders", Convert.ToInt32(dr["DocTypeId"]), Convert.ToDateTime(dr["DocDate"]), Convert.ToInt32(dr["DivisionId"]), Convert.ToInt32(dr["SiteId"]));
}
else
{
dr["DocNo"] = PrevDocNo.Substring(0, 2) + "-" + (Convert.ToInt32(PrevDocNo.Substring(PrevDocNo.IndexOf("-") + 1)) + 1).ToString().PadLeft(4, '0').ToString();
}
PrevSupplierCode = dr["SupplierCode"].ToString();
PrevDocNo = dr["DocNo"].ToString();
}
else
{
dr["DocNo"] = PrevDocNo;
}
dataTable.Rows.Add(dr);
}
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".ProcImportFinishingOrderCancelFromTextFile"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@TextFileData", dataTable);
cmd.CommandTimeout = 1000;
//cmd.Connection.Open();
//cmd.ExecuteNonQuery();
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
//cmd.Connection.Close();
}
}
List <ImportErrors> ImportErrorList = new List <ImportErrors>();
if (ds.Tables[0].Rows.Count == 0)
{
ViewBag.id = id;
return(View("Sucess"));
}
else
{
for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
{
if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
{
ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
}
//if (ds.Tables[0].Rows[j]["WarningText"].ToString() != "")
//{
// WarningText = WarningText + ds.Tables[0].Rows[j]["WarningText"].ToString() + "." + Environment.NewLine;
//}
ImportErrors ImportError = new ImportErrors();
ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
ImportError.BarCodes = ds.Tables[0].Rows[j]["BarCodes"].ToString();
ImportErrorList.Add(ImportError);
}
if (ErrorText != "")
{
ViewBag.Error = ErrorText;// +WarningText;
ViewBag.id = id;
string DataTableSessionVarName = "";
DataTableSessionVarName = User.Identity.Name.ToString() + "ImportData" + id.ToString();
Session[DataTableSessionVarName] = dataTable;
//return View("Error");
return(View("Error", ImportErrorList));
}
return(View("Sucess"));
}
}
public ActionResult DatabaseOperation(IList <MergingExcel> MergingRecordList)
{
string ErrorText = "";
DataTable dataTable = new DataTable();
dataTable.Columns.Add("MergingItem");
dataTable.Columns.Add("MainItem");
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
string UserName = User.Identity.Name;
foreach (var item in MergingRecordList)
{
var dr = dataTable.NewRow();
dr["MergingItem"] = item.MergingItem;
dr["MainItem"] = item.MainItem;
dataTable.Rows.Add(dr);
}
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".sp_MergePerson"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@ExcelFileData", dataTable);
cmd.Parameters.AddWithValue("@UserName", UserName);
cmd.CommandTimeout = 1000;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
}
}
List <ImportErrors> ImportErrorList = new List <ImportErrors>();
if (ds.Tables[0].Rows.Count == 0)
{
return(View("Sucess"));
}
else
{
for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
{
if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
{
ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
}
ImportErrors ImportError = new ImportErrors();
ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
ImportError.BarCodes = "";
ImportErrorList.Add(ImportError);
}
if (ErrorText != "")
{
ViewBag.Error = ErrorText;// +WarningText;
string DataTableSessionVarName = "";
DataTableSessionVarName = User.Identity.Name.ToString() + "ImportDataPersonMerging";
Session[DataTableSessionVarName] = dataTable;
return(View("Error", ImportErrorList));
}
return(View("Sucess"));
}
}
public ActionResult DyeingInvoiceExcelImport(int id, string WorkInvoiceNo)
{
int i = 0;
string ErrorText = "";
string DyeingConnectionString = "Persist Security Info=False;User ID='sa';pwd=P@ssw0rd!;Initial Catalog=DyeingHouse;Data Source=192.168.2.25";
DataSet WorkInvoice = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(DyeingConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("Web.FGetWorkOrderInvoice"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@WorkInvoiceNo", WorkInvoiceNo);
cmd.CommandTimeout = 1000;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(WorkInvoice);
}
}
}
DataTable dataTable = new DataTable();
dataTable.Columns.Add("DocTypeId");
dataTable.Columns.Add("DocDate");
dataTable.Columns["DocDate"].DataType = System.Type.GetType("System.DateTime");
dataTable.Columns.Add("DivisionId");
dataTable.Columns.Add("SiteId");
dataTable.Columns.Add("WorkInvoiceNo");
dataTable.Columns.Add("JobOrderNo");
dataTable.Columns.Add("JobWorkerDocNo");
dataTable.Columns.Add("JobReceiveJobWorkerDocNo");
dataTable.Columns.Add("ProductName");
dataTable.Columns.Add("Dimension1Name");
dataTable.Columns.Add("Dimension2Name");
dataTable.Columns.Add("Specification");
dataTable.Columns.Add("CreatedBy");
dataTable.Columns.Add("Qty");
dataTable.Columns.Add("Rate");
dataTable.Columns.Add("Amount");
dataTable.Columns.Add("Sr");
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
for (i = 0; i <= WorkInvoice.Tables[0].Rows.Count - 1; i++)
{
var dr = dataTable.NewRow();
dr["DocTypeId"] = id;
dr["DocDate"] = WorkInvoice.Tables[0].Rows[i]["WorkInvoiceDate"];
dr["DivisionId"] = (int)System.Web.HttpContext.Current.Session["DivisionId"];
dr["SiteId"] = (int)System.Web.HttpContext.Current.Session["SiteId"];
dr["WorkInvoiceNo"] = WorkInvoiceNo;
dr["JobOrderNo"] = WorkInvoice.Tables[0].Rows[i]["JobOrderNo"];
dr["JobWorkerDocNo"] = WorkInvoice.Tables[0].Rows[i]["JobWorkerDocNo"];
dr["JobReceiveJobWorkerDocNo"] = WorkInvoice.Tables[0].Rows[i]["JobReceiveJobWorkerDocNo"];
dr["ProductName"] = WorkInvoice.Tables[0].Rows[i]["Product"];
dr["Dimension1Name"] = WorkInvoice.Tables[0].Rows[i]["Shade"];
dr["Dimension2Name"] = WorkInvoice.Tables[0].Rows[i]["Design"];
dr["Specification"] = WorkInvoice.Tables[0].Rows[i]["Specification"];
dr["CreatedBy"] = User.Identity.Name;
dr["Qty"] = WorkInvoice.Tables[0].Rows[i]["Qty"];
dr["Rate"] = WorkInvoice.Tables[0].Rows[i]["Rate"];
dr["Amount"] = WorkInvoice.Tables[0].Rows[i]["Amount"];
dr["Sr"] = i + 1;
dataTable.Rows.Add(dr);
}
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".ProcImportDyeingInvoiceFromExcel"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@ExcelFileData", dataTable);
cmd.CommandTimeout = 1000;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
}
}
List <ImportErrors> ImportErrorList = new List <ImportErrors>();
if (ds.Tables[0].Rows.Count == 0)
{
ViewBag.id = id;
return(View("Sucess"));
}
else
{
for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
{
if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
{
ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
}
ImportErrors ImportError = new ImportErrors();
ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
ImportError.BarCodes = "";
ImportErrorList.Add(ImportError);
}
if (ErrorText != "")
{
ViewBag.Error = ErrorText;// +WarningText;
ViewBag.id = id;
string DataTableSessionVarName = "";
DataTableSessionVarName = User.Identity.Name.ToString() + "ImportData" + id.ToString();
Session[DataTableSessionVarName] = dataTable;
return(View("Error", ImportErrorList));
}
return(View("Sucess"));
}
}
public Task <List <ExternalEntryForSave> > Import(Stream fileStream, string fileName, string contentType)
{
// Validation
if (fileStream == null)
{
throw new BadRequestException(_localizer["Error_NoFileWasUploaded"]);
}
// Extract the raw data from the file stream
IEnumerable <string[]> data = ControllerUtilities.ExtractStringsFromFile(fileStream, fileName, contentType, _localizer);
if (data.Count() <= 1)
{
throw new BadRequestException(_localizer["Error_UploadedFileWasEmpty"]);
}
// Errors
var importErrors = new ImportErrors();
// Result
var result = new List <ExternalEntryForSave>();
// Go over every row and parse
foreach (var(row, rowIndex) in data.Select((e, i) => (e, i)).Skip(1))
{
var dateString = row.ElementAtOrDefault(0);
var externalRef = row.ElementAtOrDefault(1);
var amountString = row.ElementAtOrDefault(2);
// Ignore empty rows
if (string.IsNullOrWhiteSpace(dateString) && string.IsNullOrWhiteSpace(externalRef) && string.IsNullOrWhiteSpace(amountString))
{
continue;
}
var exEntry = new ExternalEntryForSave
{
ExternalReference = externalRef
};
// Parse date
if (string.IsNullOrWhiteSpace(dateString))
{
importErrors.AddImportError(rowIndex + 1, 1, _localizer[Constants.Error_Field0IsRequired, _localizer["Line_PostingDate"]]);
}
else if (DateTime.TryParse(dateString, out DateTime date))
{
exEntry.PostingDate = date;
}
else if (double.TryParse(dateString, out double d))
{
// Double indicates an OLE Automation date which typically comes from excel
exEntry.PostingDate = DateTime.FromOADate(d);
}
else
{
throw new ParseException(_localizer["Error_Value0IsNotAValid1Example2", dateString, _localizer["DateTime"], DateTime.Today.ToString("yyyy-MM-dd")]);
}
if (string.IsNullOrWhiteSpace(amountString))
{
importErrors.AddImportError(rowIndex + 1, 3, _localizer[Constants.Error_Field0IsRequired, _localizer["Entry_MonetaryValue"]]);
}
else if (decimal.TryParse(amountString, out decimal d))
{
exEntry.MonetaryValue = Math.Abs(d);
exEntry.Direction = d < 0 ? (short)-1 : (short)1;
}
else
{
throw new ParseException(_localizer["Error_Value0IsNotAValid1Example2", amountString, _localizer["Decimal"], 21502.75m]);
}
if (importErrors.IsValid)
{
result.Add(exEntry);
}
}
importErrors.ThrowIfInvalid(_localizer);
return(Task.FromResult(result));
}
public ActionResult PhysicalStockTextImport(int id, int?GodownId, int?PersonId, string Remark)
{
string[] StrArr = new string[] {};
string ErrorText = "";
if (Request.Files.Count == 0 || Request.Files[0].FileName == "")
{
ViewBag.id = id;
ModelState.AddModelError("", "Please select file.");
return(View("Index"));
}
if (GodownId == 0 || GodownId == null)
{
ViewBag.id = id;
ModelState.AddModelError("", "Please select Godown.");
return(View("Index"));
}
if (PersonId == 0 || PersonId == null)
{
ViewBag.id = id;
ModelState.AddModelError("", "Please select Person.");
return(View("Index"));
}
var file = Request.Files[0];
string filePath = Request.MapPath(ConfigurationManager.AppSettings["ExcelFilePath"] + file.FileName);
file.SaveAs(filePath);
StreamReader Sr;
Sr = new StreamReader(filePath);
string Line = "";
//List<TypeImportPhysicalStockFromTextFile> ImportData = new List<TypeImportPhysicalStockFromTextFile>();
DataTable dataTable = new DataTable();
dataTable.Columns.Add("DocTypeId");
dataTable.Columns.Add("DocDate");
dataTable.Columns["DocDate"].DataType = System.Type.GetType("System.DateTime");
dataTable.Columns.Add("DocNo");
dataTable.Columns.Add("DivisionId");
dataTable.Columns.Add("SiteId");
dataTable.Columns.Add("ProcessCode");
dataTable.Columns.Add("JobReceiveByCode");
dataTable.Columns.Add("ProductUidName");
dataTable.Columns.Add("SupplierCode");
dataTable.Columns.Add("CreatedBy");
dataTable.Columns.Add("GodownId");
dataTable.Columns.Add("DocNature");
dataTable.Columns.Add("Remark");
dataTable.Columns.Add("Sr");
string PrevSupplierCode = "";
string PrevDoNo = "";
int i = 0;
do
{
i++;
Line = Sr.ReadLine();
if (Line != null)
{
StrArr = Line.Split(new Char[] { ',' });
var dr = dataTable.NewRow();
dr["DocTypeId"] = id;
dr["DocDate"] = DateTime.Now.Date;
dr["DivisionId"] = (int)System.Web.HttpContext.Current.Session["DivisionId"];
dr["SiteId"] = (int)System.Web.HttpContext.Current.Session["SiteId"];
dr["ProductUidName"] = StrArr[5];
dr["SupplierCode"] = PersonId;
dr["CreatedBy"] = User.Identity.Name;
dr["ProcessCode"] = StrArr[3];
//dr["JobReceiveByCode"] = StrArr[11];
dr["GodownId"] = GodownId;
dr["Remark"] = Remark;
dr["DocNature"] = 'R';
dr["Sr"] = i;
dr["DocNo"] = new DocumentTypeService(_unitOfWork).FGetNewDocNo("DocNo", ConfigurationManager.AppSettings["DataBaseSchema"] + ".StockHeaders", Convert.ToInt32(dr["DocTypeId"]), Convert.ToDateTime(dr["DocDate"]), Convert.ToInt32(dr["DivisionId"]), Convert.ToInt32(dr["SiteId"]));
PrevDoNo = dr["DocNo"].ToString();
//if (PrevSupplierCode != dr["SupplierCode"].ToString())
//{
// if (PrevDoNo == "")
// {
// dr["DocNo"] = new DocumentTypeService(_unitOfWork).FGetNewDocNo("DocNo", ConfigurationManager.AppSettings["DataBaseSchema"] + ".StockHeaders", Convert.ToInt32(dr["DocTypeId"]), Convert.ToDateTime(dr["DocDate"]), Convert.ToInt32(dr["DivisionId"]), Convert.ToInt32(dr["SiteId"]));
// }
// else
// {
// //PrevDoNo.Substring(0,2) + "-" +
// //dr["DocNo"] = (PrevDoNo.Substring(PrevDoNo.IndexOf("-")+1) + 1).PadLeft(4,new Char[] { '0' }));
// dr["DocNo"] = PrevDoNo.Substring(0, 2) + "-" + (Convert.ToInt32(PrevDoNo.Substring(PrevDoNo.IndexOf("-") + 1)) + 1).ToString().PadLeft(4, '0').ToString();
// }
// PrevSupplierCode = dr["SupplierCode"].ToString();
// PrevDoNo = dr["DocNo"].ToString();
//}
//else
//{
// dr["DocNo"] = PrevDoNo;
//}
dataTable.Rows.Add(dr);
}
} while (Line != null);
Sr.Close();
//string ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString.ToString();
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".ProcImportPhysicalStockFromTextFile"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@TextFileData", dataTable);
cmd.CommandTimeout = 1000;
//cmd.Connection.Open();
//cmd.ExecuteNonQuery();
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
//cmd.Connection.Close();
}
}
List <ImportErrors> ImportErrorList = new List <ImportErrors>();
if (ds.Tables[0].Rows.Count == 0)
{
ViewBag.id = id;
return(View("Sucess"));
}
else
{
for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
{
if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
{
ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
}
//if (ds.Tables[0].Rows[j]["WarningText"].ToString() != "")
//{
// WarningText = WarningText + ds.Tables[0].Rows[j]["WarningText"].ToString() + "." + Environment.NewLine;
//}
ImportErrors ImportError = new ImportErrors();
ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
ImportErrorList.Add(ImportError);
}
if (ErrorText != "")
{
ViewBag.Error = ErrorText; // +WarningText;
ViewBag.id = id;
string DataTableSessionVarName = "";
DataTableSessionVarName = User.Identity.Name.ToString() + "ImportData" + id.ToString();
Session[DataTableSessionVarName] = dataTable;
//return View("Error");
return(View("Error", ImportErrorList));
}
return(View("Sucess"));
}
}
public ActionResult Importexcel(HttpPostedFileBase file)
{
if (Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension = Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
// Create a folder in App_Data named ExcelFiles because you need to save the file temporarily location and getting data from there.
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Content/UploadedFolder"), Request.Files["FileUpload"].FileName);
if (System.IO.File.Exists(fileLocation))
System.IO.File.Delete(fileLocation);
Request.Files["FileUpload"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
List<ImportErrors> listErrors = new List<ImportErrors>();
ImportErrors imEror = new ImportErrors();
var listUni = db.Universities.ToList();
int tmp = db.Universities.Count();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
University uni = new University();
imEror = new ImportErrors();
if (ds.Tables[0].Rows[i]["Name"].ToString() != "")
{
uni.Name = ds.Tables[0].Rows[i]["Name"].ToString();
}
else
{
imEror.Name = null;
imEror.Address = ds.Tables[0].Rows[i]["Address"].ToString();
imEror.Code = ds.Tables[0].Rows[i]["UniversityCode"].ToString();
}
if (ds.Tables[0].Rows[i]["UniversityCode"].ToString() != "")
{
uni.UniversityCode = ds.Tables[0].Rows[i]["UniversityCode"].ToString();
}
else
{
imEror.Code = null;
imEror.Name = ds.Tables[0].Rows[i]["Name"].ToString();
imEror.Address = ds.Tables[0].Rows[i]["Address"].ToString();
}
if (ds.Tables[0].Rows[i]["Address"].ToString() != "")
{
uni.Address = ds.Tables[0].Rows[i]["Address"].ToString();
}
else
{
imEror.Address = null;
imEror.Name = ds.Tables[0].Rows[i]["Name"].ToString();
imEror.Code = ds.Tables[0].Rows[i]["UniversityCode"].ToString();
}
uni.Website = ds.Tables[0].Rows[i]["Website"].ToString() != "" ? ds.Tables[0].Rows[i]["Website"].ToString() : null;
uni.Phone = ds.Tables[0].Rows[i]["Phone"].ToString() != "" ? ds.Tables[0].Rows[i]["Phone"].ToString() : null;
bool flag = true;
if (uni.Name != null && uni.Address != null && uni.UniversityCode != null)
{
for (int j = 0; j < tmp; j++)
{
imEror = new ImportErrors();
if (ds.Tables[0].Rows[i]["Name"].ToString() == listUni[j].Name)
{
imEror.SameName = uni.Name;
imEror.Name = uni.Name;
imEror.Code = uni.UniversityCode;
imEror.Address = uni.Address;
flag = false;
}
if (ds.Tables[0].Rows[i]["UniversityCode"].ToString() == listUni[j].UniversityCode)
{
imEror.SameCode = uni.UniversityCode;
imEror.Code = uni.UniversityCode;
imEror.Name = uni.Name;
imEror.Address = uni.Address;
flag = false;
}
if (imEror.SameCode != null || imEror.SameName != null)
{
imEror.Row = i + 2;
listErrors.Add(imEror);
break;
}
}
if (flag)
{
db.Universities.Add(uni);
db.SaveChanges();
}
}
else
{
imEror.Row = i + 2;
listErrors.Add(imEror);
}
}
if (listErrors.Count > 0)
{
TempData["listerror"] = listErrors;
return RedirectToAction("ImportError");
}
return RedirectToAction("ManageUniversity");
//ViewBag.message = "Information saved successfully.";
}
ModelState.AddModelError("", "Plese select Excel File.");
}
return RedirectToAction("ManageUniversity");
}
public ActionResult PhysicalStockAdjustment(int id, DateTime?DocDate, int?GodownId, string DateList, string Remark)
{
string[] StrArr = new string[] {};
string ErrorText = "";
//string WarningText = "";
if (DocDate == null)
{
ViewBag.id = id;
ModelState.AddModelError("", "Please select Doc Date.");
return(View("Index"));
}
if (GodownId == 0 || GodownId == null)
{
ViewBag.id = id;
ModelState.AddModelError("", "Please select Godown.");
return(View("Index"));
}
if (DateList == "" || DateList == null)
{
//ViewBag.id = id;
//ModelState.AddModelError("", "Please select Physical Dates.");
//return View("Index");
DateList = DocDate.ToString();
}
string CreatedBy = User.Identity.Name;
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".sp_PhysicalStockAdjustment"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@DocTypeId", id);
cmd.Parameters.AddWithValue("@DocDate", DocDate);
cmd.Parameters.AddWithValue("@GodownId", GodownId);
cmd.Parameters.AddWithValue("@CreatedBy", CreatedBy);
cmd.Parameters.AddWithValue("@DivisionId", DivisionId);
cmd.Parameters.AddWithValue("@SiteId", SiteId);
cmd.Parameters.AddWithValue("@PhysicalDateList", DateList);
cmd.Parameters.AddWithValue("@Remark", Remark);
cmd.CommandTimeout = 1000;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
//cmd.Connection.Close();
}
}
List <ImportErrors> ImportErrorList = new List <ImportErrors>();
if (ds.Tables[0].Rows.Count == 0)
{
ViewBag.id = id;
return(View("Sucess"));
}
else
{
for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
{
if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
{
ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
}
//if (ds.Tables[0].Rows[j]["WarningText"].ToString() != "")
//{
// WarningText = WarningText + ds.Tables[0].Rows[j]["WarningText"].ToString() + "." + Environment.NewLine;
//}
ImportErrors ImportError = new ImportErrors();
ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
ImportError.BarCodes = "";
ImportErrorList.Add(ImportError);
}
if (ErrorText != "")
{
ViewBag.Error = ErrorText; // +WarningText;
ViewBag.id = id;
return(View("Error", ImportErrorList));
}
return(View("Sucess"));
}
}
/// <summary>
/// Executes this instance.
/// </summary>
public override void Execute()
{
var configService = ServiceLocator.Current.GetInstance <IConfigurationService>();
var obj = new object[] { };
var version = DateTime.Now.Ticks;
NumberOfErrors = 0;
AssertErrorFile();
var startTime = DateTime.Now;
var states = configService.HospitalRegion.DefaultStates.OfType <string>().ToList();
var dlg = new Microsoft.Win32.OpenFileDialog
{
DefaultExt = FileExtension,
Filter = "csv files (*.csv)|*.csv|All files (*.*)|*.*"
};
if (!dlg.ShowDialog().GetValueOrDefault())
{
return;
}
if (PromptUserForContinue() == MessageBoxResult.No)
{
return;
}
if (!ValidateImportFileName(dlg.FileName))
{
var message = string.Format("The file \"{0}\" could not be import due to the name containing special characters and/or dashes (-). Please remove these special characters and dashes to spaces and/or underscores and try again.", Path.GetFileName(dlg.FileName));
MessageBox.Show(message, "MONAHRQ Import File Open Error", MessageBoxButton.OK);
return;
}
try
{
OnImporting(this, EventArgs.Empty);
BeginImport();
var uploadFilePath = dlg.FileName;
//var fileName = Regex.Replace(Path.GetFileName(uploadFilePath), "[^a-zA-Z0-9_]+", "_").Replace("-", "_");
var tb = new DataTable("Temp");
lock (obj)
{
using (var connection = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source= " +
Path.GetDirectoryName(uploadFilePath) + "; Extended Properties= \"Text;HDR=YES;FMT=Delimited\""))
{
connection.Open();
using (var cmd = new OleDbCommand("SELECT * FROM " + Path.GetFileName(uploadFilePath), connection))
{
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
tb.Load(reader);
}
}
}
}
}
var actualStates = tb.Rows.OfType <DataRow>()
.Where(dr => dr["State"] != DBNull.Value)
.Select(dr => dr["State"].ToString())
.Distinct()
.ToList();
int rowCount = 0;
foreach (DataRow row in tb.Rows)
{
var state = row["State"].ToString();
long npi = 0;
if (row["NPI"] != DBNull.Value)
{
npi = long.Parse(row["NPI"].ToString());
}
if (!states.Contains(state))
{
var message = new StringBuilder();
message.AppendLine(
string.Format(
"The Physician record being imported is not with in the selected state context of \"{0}\"",
string.Join(",", states)));
message.AppendLine(string.Format("Data: {0}", string.Join(",", row.ItemArray)));
ImportErrors.Add(ImportError.Create("Physician", "Physician", message.ToString()));
continue;
}
if (CheckIfAlreadyExists(npi, state))
{
var message = new StringBuilder();
message.AppendLine(
string.Format(
"The Physician record with NPI \"{0}\" already exists and can't be imported. Please choose an unique NPI number.",
npi));
message.AppendLine(string.Format("Data: {0}", string.Join(",", row.ItemArray)));
ImportErrors.Add(ImportError.Create("Physician", "Physician", message.ToString()));
continue;
}
var tRow = _physiciansTable.NewRow();
tRow["Npi"] = npi;
tRow["PacId"] = row["PAC ID"];
tRow["ProfEnrollId"] = row["Professional Enrollment ID"];
tRow["LastName"] = row["Last Name"];
tRow["FirstName"] = row["First Name"];
tRow["MiddleName"] = row["Middle Name"];
tRow["Suffix"] = row["Suffix"];
tRow["Gender"] = row["Gender"];
tRow["Credential"] = row["Credential"];
tRow["MedicalSchoolName"] = row["Medical school name"];
tRow["GraduationYear"] = row["Graduation year"];
tRow["PrimarySpecialty"] = row["Primary specialty"];
tRow["SecondarySpecialty1"] = row["Secondary specialty 1"];
tRow["SecondarySpecialty2"] = row["Secondary specialty 2"];
tRow["SecondarySpecialty3"] = row["Secondary specialty 3"];
tRow["SecondarySpecialty4"] = row["Secondary specialty 4"];
tRow["AllSecondarySpecialties"] = row["All secondary specialties"];
tRow["OrgLegalName"] = row["Organization legal name"];
tRow["DBAName"] = DBNull.Value; // row["Organization DBA name"];
tRow["GroupPracticePacId"] = row["Group Practice PAC ID"];
tRow["NumberofGroupPracticeMembers"] = row["Number of Group Practice members"];
tRow["Line1"] = row["Line 1 Street Address"];
tRow["Line2"] = row["Line 2 Street Address"];
tRow["MarkerofAdressLine2Suppression"] = row["Marker of address line 2 suppression"] ==
DBNull.Value ||
row["Marker of address line 2 suppression"]
.ToString() == "N"
? 0
: 1;
tRow["City"] = row["City"];
tRow["State"] = row["State"];
tRow["ZipCode"] = row["Zip Code"];
if (row["Hospital affiliation CCN 1"] != DBNull.Value)
{
tRow["HospitalAffiliationCCN1"] = row["Hospital affiliation CCN 1"].ToString().PadLeft(6, '0');
}
tRow["HospitalAffiliationLBN1"] = row["Hospital affiliation LBN 1"];
if (row["Hospital affiliation CCN 2"] != DBNull.Value)
{
tRow["HospitalAffiliationCCN2"] = row["Hospital affiliation CCN 2"].ToString().PadLeft(6, '0');
}
tRow["HospitalAffiliationLBN2"] = row["Hospital affiliation LBN 2"];
if (row["Hospital affiliation CCN 3"] != DBNull.Value)
{
tRow["HospitalAffiliationCCN3"] = row["Hospital affiliation CCN 3"].ToString().PadLeft(6, '0');
}
tRow["HospitalAffiliationLBN3"] = row["Hospital affiliation LBN 3"];
if (row["Hospital affiliation CCN 4"] != DBNull.Value)
{
tRow["HospitalAffiliationCCN4"] = row["Hospital affiliation CCN 4"].ToString().PadLeft(6, '0');
}
tRow["HospitalAffiliationLBN4"] = row["Hospital affiliation LBN 4"];
if (row["Hospital affiliation CCN 5"] != DBNull.Value)
{
tRow["HospitalAffiliationCCN5"] = row["Hospital affiliation CCN 5"].ToString().PadLeft(6, '0');
}
tRow["HospitalAffiliationLBN5"] = row["Hospital affiliation LBN 5"];
tRow["AcceptsMedicareAssignment"] = row["Professional accepts Medicare Assignment"];
tRow["ParticipatesInERX"] = DBNull.Value;
tRow["ParticipatesInPQRS"] = row["Reported Quality Measures"] == DBNull.Value ||
row["Reported Quality Measures"].ToString() == "N"
? 0
: 1;
tRow["ParticipatesInEHR"] = row["Used electronic health records"] == DBNull.Value ||
row["Used electronic health records"].ToString() == "N"
? 0
: 1;
//tRow["ParticipatesInERX"] = row["Participating in eRx"] == DBNull.Value ||
// row["Participating in eRx"].ToString() == "N"
// ? 0
// : 1;
//tRow["ParticipatesInPQRS"] = row["Participating in PQRS"] == DBNull.Value ||
// row["Participating in PQRS"].ToString() == "N"
// ? 0
// : 1;
//tRow["ParticipatesInEHR"] = row["Participating in EHR"] == DBNull.Value ||
// row["Participating in EHR"].ToString() == "N"
// ? 0
// : 1;
tRow["IsEdited"] = 1;
_physiciansTable.Rows.Add(tRow);
rowCount++;
Events.GetEvent <PleaseStandByMessageUpdateEvent>()
.Publish("Importing line " + rowCount.ToString());
}
//if (_physiciansTable.Rows.Count == BATCH_SIZE)
//{
BulkInsert(_physiciansTable, _configurationService.ConnectionSettings.ConnectionString,
_physiciansTable.Rows.Count);
CountInserted = _physiciansTable.Rows.Count;
//}_
foreach (var contextState in actualStates)
{
var physicianScript = ReflectionHelper.ReadEmbeddedResourceFile(GetType().Assembly,
"Monahrq.Sdk.Resources.PhysiciansImport.ImportUpdatePhysicians.sql")
.Replace("[@@State@@]", contextState);
var medPracticeScript = ReflectionHelper.ReadEmbeddedResourceFile(GetType().Assembly,
"Monahrq.Sdk.Resources.PhysiciansImport.ImportUpdateMedicalPractices.sql")
.Replace("[@@State@@]", contextState);
var physicianMedPracticeScript = ReflectionHelper.ReadEmbeddedResourceFile(GetType().Assembly,
"Monahrq.Sdk.Resources.PhysiciansImport.ImportUpdatePhysiciansMedPractices.sql")
.Replace("[@@State@@]", contextState);
var completQuery = new StringBuilder();
completQuery.AppendLine(physicianScript);
completQuery.AppendLine(medPracticeScript);
completQuery.AppendLine(physicianMedPracticeScript);
var combinedQueries = completQuery.ToString();
using (var session = Provider.SessionFactory.OpenStatelessSession())
{
using (var trans = session.BeginTransaction())
{
session.CreateSQLQuery(combinedQueries)
.SetTimeout(10000)
.ExecuteUpdate();
trans.Commit();
}
}
//CountInserted = GetTotalItemsSaved(typeof(Physician), "Npi",
// string.Format("Where [States]='{0}';", contextState));
//var totalMedicalPracticesSaved = GetTotalItemsSaved(typeof (MedicalPractice), "GroupPracticePacId",
// string.Format("Where [State]='{0}';",
// contextState));
}
Events.GetEvent <PleaseStandByMessageUpdateEvent>().Publish("Finalizing import...");
// what is the purpose of this delay???
const int MAX_DELAY_SECONDS = 3;
var elapsed = DateTime.Now - startTime;
var seconds = elapsed.TotalSeconds;
var remaining = MAX_DELAY_SECONDS - seconds;
if (remaining > 0)
{
Thread.Sleep(TimeSpan.FromSeconds(remaining));
}
}
catch (IOException exc)
{
Logger.Write(exc, "Error importing data from file {0}", dlg.FileName);
var message = string.Format("Please close file\"{0}\" before trying to import.",
dlg.FileName.SubStrAfterLast(@"\"));
MessageBox.Show(message, "MONAHRQ Import File Open Error", MessageBoxButton.OK);
}
catch (Exception exc)
{
Logger.Write(exc, "Error importing data from file {0}", dlg.FileName);
ImportErrors.Add(ImportError.Create("Physician", "Physician", exc.GetBaseException().Message));
}
finally
{
EndImport();
if (ImportErrors.Any())
{
//Task.Factory.StartNew(() =>
//{
using (var sw = new StreamWriter(ErrorFile, true))
{
foreach (var error in ImportErrors)
{
WriteError2(sw, error.ErrorMessage);
sw.WriteLine();
NumberOfErrors++;
}
}
//});
}
OnImported(this, EventArgs.Empty);
Events.GetEvent <PleaseStandByMessageUpdateEvent>().Publish("Import Complete");
Events.GetEvent <SimpleImportCompletedEvent>().Publish(this);
}
}
