private void sImportPersons()
{
// AR 04-05-2017
try {
DateTime vCurrentDate = BasicClass.fGetCurDateTime();
int x = vFirstEmpRow;
this.sCopyRowTo(vWorkSheet, vErrorsWorkSheet, 1, (vErrorFound + 1), 1, BasicClass.fGetCurDateTime(), "");
// testing getting value
string vName = "";
string vAddress = "";
string vMobileNumber = "";
string vEducation = "";
string vBirthdate = "";
string vGender = "";
int vErrorCol = 0;
string vErrorMessage = "";
Microsoft.Office.Interop.Excel.Range vRange;
vRange = vWorkSheet.UsedRange;
for (x = vFirstEmpRow; (x <= vRange.Rows.Count); x++)
{
if ((vWorkSheet.Cells[x, vNameCol].Value != null))
{
vErrorCol = 0;
if ((vWorkSheet.Cells[x, vNameCol].Value == null))
{
vErrorCol = vNameCol;
vErrorMessage = "يرجى اختيار الاسم";
}
else
{
if (vWorkSheet.Cells[x, vNameCol].Value != null)
{
vName = vWorkSheet.Cells[x, vNameCol].Value.ToString();
}
}
//////////if (isAlphabetsWord(vWorkSheet.Cells[x, vNameCol].Value.ToString()) == false)
//////////{
////////// vErrorCol = vNameCol;
////////// vErrorMessage = "خطأ فى الاسم";
//////////}
if (vAddressCol > 0)
{
if ((vWorkSheet.Cells[x, vAddressCol].Value == null))
{
vErrorCol = vAddressCol;
vErrorMessage = "يرجى اختيار العنوان";
}
else
{
if (vWorkSheet.Cells[x, vAddressCol].Value != null)
{
vAddress = vWorkSheet.Cells[x, vAddressCol].Value.ToString();
}
}
}
if (vMobileNoCol > 0)
{
if ((vWorkSheet.Cells[x, vMobileNoCol].Value == null))
{
vErrorCol = vMobileNoCol;
vErrorMessage = "يرجى اختيار رقم الموبايل";
}
else if (isPhoneNumber(vWorkSheet.Cells[x, vMobileNoCol].Value.ToString()) == false)
{
vErrorCol = vMobileNoCol;
vErrorMessage = "خطأ فى رقم الموبايل";
}
else
{
if (vWorkSheet.Cells[x, vMobileNoCol].Value != null)
{
vMobileNumber = vWorkSheet.Cells[x, vMobileNoCol].Value.ToString();
}
}
}
if (vBirthDateCol > 0)
{
if ((vWorkSheet.Cells[x, vBirthDateCol].Value == null))
{
vErrorCol = vBirthDateCol;
vErrorMessage = "يرجى اختيار تاريخ الميلاد";
}
else
{
string xx = vWorkSheet.Cells[x, vBirthDateCol].Value.ToString();
if (xx.Contains(@"^[a-zA-Z]+$") == false)
{
DateTime time = DateTime.Parse(vWorkSheet.Cells[x, vBirthDateCol].Value.ToString());
string format = "yyyy-MM-dd";
// DateTime dt = DateTime.Parse(TXT_Date.Text);
vBirthdate = time.ToString(format);
}
}
}
if (vEducationCol > 0)
{
if ((vWorkSheet.Cells[x, vEducationCol].Value == null))
{
vErrorCol = vEducationCol;
vErrorMessage = "يرجى اختيار المؤهل الدراسي";
}
else
{
if (vWorkSheet.Cells[x, vEducationCol].Value != null)
{
vEducation = vWorkSheet.Cells[x, vEducationCol].Value.ToString();
}
}
}
if (vGenderCol > 0)
{
if ((vWorkSheet.Cells[x, vGenderCol].Value == null))
{
vErrorCol = vGenderCol;
vErrorMessage = "يرجى اختيار النوع";
}
else
{
vGender = vWorkSheet.Cells[x, vGenderCol].Value;
if (vGender.ToString().Trim().Contains("كر") == true || vGender.ToString().Trim().Equals("M") == true)
{
vGender = "M";
}
else if (vGender.ToString().Trim().Contains("نث") == true || vGender.ToString().Trim().Equals("F") == true)
{
vGender = "F";
}
}
}
if ((vErrorCol != 0))
{
vErrorFound++;
this.sCopyRowTo(vWorkSheet, vErrorsWorkSheet, x, (vErrorFound + 1), vErrorCol, BasicClass.fGetCurDateTime(), vErrorMessage);
// TODO: Labeled Arguments not supported. Argument: 6 := 'pErrorMessage'
}
else
{
cHumanDetails vHumanItem = new cHumanDetails();
vHumanItem.vName = vName;
vHumanItem.vAddress = vAddress;
vHumanItem.vMobileNo = vMobileNumber;
vHumanItem.vEducation = vEducation;
vHumanItem.vBirthDate = vBirthdate;
vHumanItem.vGender = vGender;
vHumanItem.vIndex = vLstData.Count;
// AR ADDED 10-04-2017
// ---------------------------------------------------------------------------------------
//if ((this.fIsItemExistAgainOnGrid(vCountItem) == false)) {
// this.sGetAvailableQtyWithCost(vCountItem, vTDate, vStrCode);
// // TODO: Labeled Arguments not supported. Argument: 2 := 'pTDate'
// // TODO: Labeled Arguments not supported. Argument: 3 := 'pStrCode'
//}
// ---------------------------------------------------------------------------------------
vLstData.Add(vHumanItem);
}
// Increase Counter
vItemsCount++;
}
// x += 1
// get new values
vName = vWorkSheet.Cells[x, vNameCol].Value;
}
// excute databse
int vSuccessCount = (vItemsCount - vErrorFound);
if ((vSuccessCount > 0))
{
if ((MessageBox.Show(("تم استيراد البيانات " + ("\r\n"
+ (vSuccessCount.ToString() + (" من "
+ (vItemsCount.ToString() + (" عدد " + ("\r\n" + "هل تريد الاستمرار ؟"))))))), "استعلام عن العملية", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.Yes))
{
// HERE I INSERT DATA INTO DATA BASE
string vsqlstmt = "";
foreach (var item in vLstData)
{
if (item != null)
{
vsqlstmt = "INSERT INTO [dbo].[TBL_MainPerson]" + "\n" +
" ([Church_ID]" + "\n" +
" ,[City_ID]" + "\n" +
" ,[PersType_ID]" + "\n" +
" ,[Name]" + "\n" +
" ,[AdressDisc]" + "\n" +
" ,[Mobile]" + "\n" +
" ,[BirthDate]" + "\n" +
" ,[Gender]" + "\n" +
" )" + "\n" +
" VALUES " + "\n" +
" (" + BasicClass.vChurchID + ", " + "\n" +
" " + BasicClass.vCityID + ", " + "\n" +
" 3, " + "\n" +
" '" + item.vName + "', " + "\n" +
" '" + item.vAddress + "', " + "\n" +
" '" + item.vMobileNo + "', " + "\n" +
" '" + item.vBirthDate + "', " + "\n" +
" '" + item.vGender + "' " + "\n" +
" ) " + "\n";
sFillSqlStatmentArray(vsqlstmt);
}
}
long vRowsAffected = 0;
vRowsAffected = BasicClass.fDMLData(vSqlStatment, "cImportExcel");
if (vRowsAffected > 0)
{
MessageBox.Show("تم ادخال البيانات بنجاح", "نجاح", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
}
}
else
{
vIsUserConfirm = false;
}
}
else
{
vIsUserConfirm = false;
}
// output errors
if ((vErrorFound >= 1))
{
// MessageBox.Show(vCollectErrors)
vErrorsWorkSheet.Activate();
}
else
{
// vErrorsWorkSheet.Delete()
}
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
vWorkBook.Close(false);
vXLS.Quit();
}
finally {
BasicClass.fReturnNonQuery(("ALTER DATABASE "
+ (BasicClass.vSqlConn.Database + " SET MULTI_USER WITH ROLLBACK IMMEDIATE")), "");
}
}