private ADODB.Recordset FillTextFileToRecordset(string _sDir, string _sFile, string _sDesPath, ADODB.Connection _ad_Con)
{
ADODB.Recordset rs = new ADODB.RecordsetClass();
try
{
string s;
StreamReader strR = new StreamReader(_sDir + "\\" + _sFile);
StreamWriter strW = new StreamWriter(_sDesPath + "\\" + _sFile);
strW.Write(strR.ReadToEnd());
strR.Close();
strW.Close();
WriteSchemaFile(_sDir, _sFile, _sDesPath);
s = "SELECT * FROM [" + _sFile + "]";
rs.Open(s, _ad_Con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, -1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(rs);
}
private void Transfer(ListBox _lbxFile, ListBox _lbxRecord, Label _lbNumRec, int _iSEQ_NO, ref bool _bFin)
{
// bool bContinue = false;
SqlConnection SqlCon = new SqlConnection(PublicFunction.C_con.ConnectionString);
SqlCon.Open();
string str_Con; //string connection
ADODB.Connection ad_Con = new ADODB.ConnectionClass();
ADODB.Recordset ad_Rs = new ADODB.RecordsetClass();
string S_sDir = txt_path1.Text; //T_String.GetDataFromSQL("DIR_DR","GP_SYS_READER","SEQ_NO='" + _iSEQ_NO + "'", SqlCon);
string S_sFile = txt_file1.Text; //T_String.GetDataFromSQL("FIL_NM", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon);
string sDateFormat = PublicFunction.GetOption("SCHEMA_DateFormat");
string sDouble = "";
sDouble = PublicFunction.GetOption("SCHEMA_DOUBLE");
//int S_dbBook = T_String.IsNullTo0(T_String.GetDataFromSQL("RCD_FG", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon));
string S_sAppPath = Application.StartupPath.ToString();
if (!GetFileName(_lbxFile, S_sDir, S_sFile, SqlCon))
{
_bFin = true;
return;
}
if (!CreateTextFolder(S_sAppPath, _iSEQ_NO))
{
_bFin = true;
return;
}
str_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + S_sAppPath + "\\PieceRateText" + _iSEQ_NO.ToString() + ";Extended Properties='text;HDR=Yes;FMT=Delimited';";
if (!ADODB_Connect(ad_Con, str_Con))
{
_bFin = true;
return;
}
for (int i = 0; i < _lbxFile.Items.Count; i++)
{
_lbxFile.SelectedIndex = i;
ad_Rs = FillTextFileToRecordset(S_sDir, _lbxFile.Items[i].ToString(), S_sAppPath + "\\PieceRateText" + _iSEQ_NO.ToString(), ad_Con);
if (ad_Rs.EOF)
{
}
else
{
while (ad_Rs.EOF == false)
{
string REA_NO;
string sql, EMP_ID;
double LSP;
if (stop)
{
ad_Rs.Close();
ADODB_Close(ad_Con);
return;
}
REA_NO = ad_Rs.Fields["READER_NO"].Value.ToString();
EMP_ID = REA_NO.Substring(12, 5);
LSP = T_String.IsNullTo00(REA_NO.Substring(19, REA_NO.Length - 19));
// CRD_NO = ad_Rs.Fields["CARD_NO"].Value.ToString();
// CRD_DT=ad_Rs.Fields["DAYS"].Value.ToString();
// CRD_DT.Trim();
try
{
sql = "UPDATE FILD02A SET PieceRate=" + LSP + " WHERE EMP_ID='" + EMP_ID + "' AND YYY_MM='" + dt1.Text + "' AND SEQ_NO=2";
try
{
PublicFunction.SQL_Execute(sql, SqlCon);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
catch (Exception ex)
{
// if (bContinue == false)
// {
if (MessageBox.Show(ex.Message + "\n\rText File: " + _lbxFile.Items[i].ToString() +
"\r\nContinue!", "Error!", System.Windows.Forms.MessageBoxButtons.YesNo) == DialogResult.No)
{
stop = true;
}
// bContinue = true;
// }
}
_lbxRecord.Items.Add(EMP_ID + " " + LSP.ToString());
_lbxRecord.SelectedIndex = _lbxRecord.Items.Count - 1;
// _lbNumRec.Text = (ad_Rs.Bookmark ) + "/" + ad_Rs.RecordCount.ToString();
// _lbNumRec.Text = (S_dbBook.ToString()+1) + "/" + ad_Rs.RecordCount.ToString();
ad_Rs.MoveNext();
}
}
}
_bFin = true;
SqlCon.Close();
ad_Rs.Close();
ADODB_Close(ad_Con);
}
private void TransferY(ListBox _lbxFile, ListBox _lbxRecord, Label _lbNumRec, int _iSEQ_NO, ref bool _bFin)
{
// bool bContinue = false;
SqlConnection SqlCon = new SqlConnection(PublicFunction.C_con.ConnectionString);
SqlCon.Open();
string keyD = "";
string stype = "";
int iphut = 0;
string str_Con; //string connection
ADODB.Connection ad_Con = new ADODB.ConnectionClass();
ADODB.Recordset ad_Rs = new ADODB.RecordsetClass();
string S_sDir = T_String.GetDataFromSQL("DIR_DR", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon);
string S_sFile = T_String.GetDataFromSQL("FIL_NM", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon);
string sDateFormat = PublicFunction.GetOption("SCHEMA_DateFormat");
string sDouble = "";
sDouble = PublicFunction.GetOption("SCHEMA_DOUBLE");
int S_dbBook = T_String.IsNullTo0(T_String.GetDataFromSQL("RCD_FG", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon));
string S_sAppPath = Application.StartupPath.ToString();
//key
SqlConnection SqlCon1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
SqlCon1.Open();
Func.RecordSet rsK = new Func.RecordSet("Select * from GP_KEY", SqlCon1);
if (rsK.rows > 0)
{
stype = rsK.record(0, "TYP_MN");
keyD = PublicFunction.encode(rsK.record(0, "COL_DT"), "");
iphut = T_String.IsNullTo0(rsK.record(0, "COL_MN"));
}
//end
DateTime dtFG = AQ800(PublicFunction.S_Left(GetName(S_sFile), 8));
if (dtFG == new DateTime(1, 1, 1)) //if(PublicFunction.CUS_ID=="400")
{
if (!GetFileNameTHU(_lbxFile, S_sDir, S_sFile, SqlCon))
{
_bFin = true;
return;
}
}
else
{
if (!GetFileName(_lbxFile, S_sDir, S_sFile, SqlCon))
{
_bFin = true;
return;
}
}
if (!CreateTextFolder(S_sAppPath, _iSEQ_NO))
{
_bFin = true;
return;
}
str_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + S_sAppPath + "\\Text" + _iSEQ_NO.ToString() + ";Extended Properties='text;HDR=Yes;FMT=Delimited';";
if (!ADODB_Connect(ad_Con, str_Con))
{
_bFin = true;
return;
}
for (int i = 0; i < _lbxFile.Items.Count; i++)
{
string sREA_NO = string.Empty;
if (dtFG == new DateTime(1, 1, 1)) //if(PublicFunction.CUS_ID=="400")//su dung may van tay
{
sREA_NO = _lbxFile.Items[i].ToString();
sREA_NO = PublicFunction.S_Left(sREA_NO, sREA_NO.Length - 12);
}
_lbxFile.SelectedIndex = i;
ad_Rs = FillTextFileToRecordset(S_sDir, _lbxFile.Items[i].ToString(), S_sAppPath + "\\Text" + _iSEQ_NO.ToString(), ad_Con);
if (ad_Rs.EOF || (S_dbBook > ad_Rs.RecordCount && GetName(S_sFile) == _lbxFile.Items[i].ToString()))
{
if (i < _lbxFile.Items.Count - 1)
{
S_dbBook = 1;
}
}
else
{
if (i > 0)
{
S_dbBook = 0;
}
if (S_dbBook > 0)
{
ad_Rs.Move(S_dbBook - 1, 1);
}
while (ad_Rs.EOF == false)
{
string REA_NO, CRD_NO, CRD_DT, CRD_TM, CRD_TM_02 = "";
string sql, EMP_ID;
double TM;
if (stop)
{
sql = "UPDATE GP_SYS_READER SET RCD_FG=" + S_dbBook + ",FIL_NM=N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "' where SEQ_NO='" + _iSEQ_NO + "'";
PublicFunction.SQL_Execute(sql, SqlCon);
ad_Rs.Close();
ADODB_Close(ad_Con);
return;
}
REA_NO = ad_Rs.Fields["READER_NO"].Value.ToString();
CRD_NO = ad_Rs.Fields["CARD_NO"].Value.ToString();
CRD_DT = ad_Rs.Fields["DAYS"].Value.ToString();
CRD_DT.Trim();
CRD_TM = "";
if (PublicFunction.CUS_ID == "300" && _iSEQ_NO == 1) //KenYa
{
string sdateh = "";
sdateh = CRD_DT;
CRD_NO = "00" + CRD_NO;
CRD_DT = sdateh.Substring(0, 10);
CRD_DT = CRD_DT.Replace("/", "");
CRD_TM = sdateh.Remove(0, 11);
CRD_TM = CRD_TM.Replace(":", "");
}
if (dtFG == new DateTime(1, 1, 1)) //if(PublicFunction.CUS_ID=="400")//may van tay
{
REA_NO = sREA_NO;
}
if (sDateFormat == "" || sDateFormat == null)
{
CRD_DT = ad_Rs.Fields["DAYS"].Value.ToString().Replace("/", "").Replace("-", "");
}
else if (CRD_DT != "" && sDateFormat == "fr-FR")
{
try
{
IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
// Alternate choice: If the string has been input by an end user, you might
// want to format it according to the current culture:
// IFormatProvider culture = System.Threading.Thread.CurrentThread.CurrentCulture;
CRD_DT = DateTime.Parse(CRD_DT, culture, System.Globalization.DateTimeStyles.None).ToString("yyyyMMdd");
// CRD_DT = CRD_DT.Split("d",2);//DateTime.Parse(CRD_DT).ToString() ;// .ToString(sDateFormat);
// CRD_DT = DateTime.Parse(CRD_DT).ToString("yyyyMMdd");
}
catch
{
}
}
if (PublicFunction.CUS_ID != "300" || _iSEQ_NO != 1) //kenya
{
CRD_TM = ad_Rs.Fields["HOURS"].Value.ToString().Replace(":", "");
}
try
{
if (CRD_NO != "" && CRD_TM != "" && CRD_DT != "")
{
DateTime dt1 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(-CRD_MN);
DateTime dt2 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(CRD_MN);
TM = T_String.IsNullTo00(CRD_TM);
if (TM == 0)
{
TM = 2400.0;
CRD_TM = "2400";
CRD_DT = GetDate(CRD_DT).AddDays(-1).ToString("yyyyMMdd");
}
sql = "(DAT_TM>" + dt1.ToString("yyyyMMddHHmm") + " and DAT_TM<" + dt2.ToString("yyyyMMddHHmm")
+ ") and CRD_NO=N'" + CRD_NO + "'";
if (!PublicFunction.SqlExists(SqlCon, "FILC01A", "CRD_DT='" + CRD_DT + "' AND CRD_NO='" + CRD_NO + "'"
+ " AND (DAT_TM BETWEEN '" + dt1.ToString("yyyyMMddHHmm") + "' AND '" + dt2.ToString("yyyyMMddHHmm") + "' OR CRD_TM=" + TM + ")"))
{
// if(T_String.IsNullTo0(T_String.GetDataFromSQL("top 1 1 ","FILC01A",sql, SqlCon))<=0 &&
// T_String.IsNullTo0(T_String.GetDataFromSQL("top 1 1", "FILC01A", "DAT_TM=N'"+CRD_DT+CRD_TM+"' and CRD_NO=N'"+CRD_NO+"'", SqlCon))<=0)
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
//==============================CHIA 2 TH DOC DU LIEU CHAM CONG VA DOC DU LIEU CHO MAY QUAN LY TOILET
if (sReaNo_VS1.IndexOf(REA_NO, 0) < 0) //(REA_NO != sReaNo_VS1 && REA_NO != sReaNo_VS2 )
{
sql = "Insert into FILC01A(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
sql += "N'" + CRD_DT + CRD_TM + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + TM + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "')";
}
else
{
sql = "Insert into FILC01A_VS(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
sql += "N'" + CRD_DT + CRD_TM + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + TM + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "')";
}
// sql="Insert into FILC01A(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
// sql+="N'"+CRD_DT+CRD_TM+"',N'"+EMP_ID+"','"+dt+"',N'"+PublicFunction.A_UserID+"',";
// sql+="'"+CRD_DT+"',"+TM+",";
// sql+="N'"+CRD_NO+"',N'"+REA_NO+"',";
// sql+="N'"+ S_sDir + "\\" + _lbxFile.Items[i].ToString() +"')";
try
{
PublicFunction.SQL_Execute(sql, SqlCon);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
if (sDouble != "" && sDouble != null)
{
try
{
CRD_TM_02 = ad_Rs.Fields["HOURS_02"].Value.ToString().Replace(":", "");
}
catch
{
}
}
if (sDouble != "" && sDouble != null && CRD_TM_02 != "")
{
dt1 = GetDateTime(CRD_DT, CRD_TM_02).AddMinutes(-CRD_MN);
dt2 = GetDateTime(CRD_DT, CRD_TM_02).AddMinutes(CRD_MN);
TM = T_String.IsNullTo00(CRD_TM_02);
if (TM == 0)
{
TM = 2400.0;
CRD_TM = "2400";
CRD_DT = GetDate(CRD_DT).AddDays(-1).ToString("yyyyMMdd");
}
sql = "(DAT_TM>" + dt1.ToString("yyyyMMddHHmm") + " and DAT_TM<" + dt2.ToString("yyyyMMddHHmm")
+ ") and CRD_NO=N'" + CRD_NO + "'";
// if(T_String.IsNullTo0(T_String.GetDataFromSQL(" top 1 1","FILC01A",sql, SqlCon))<=0 && //COUNT(DAT_TM)
// T_String.IsNullTo0(T_String.GetDataFromSQL("top 1 1", "FILC01A", "DAT_TM=N'"+CRD_DT+CRD_TM_02+"' and CRD_NO=N'"+CRD_NO+"'", SqlCon))<=0) //COUNT(EMP_ID)
if (!PublicFunction.SqlExists(SqlCon, "FILC01A", "CRD_DT='" + CRD_DT + "' AND CRD_NO='" + CRD_NO + "'"
+ " AND (DAT_TM BETWEEN '" + dt1.ToString("yyyyMMddHHmm") + "' AND '" + dt2.ToString("yyyyMMddHHmm") + "' OR CRD_TM=" + TM + ")"))
{
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
//==============================CHIA 2 TH DOC DU LIEU CHAM CONG VA DOC DU LIEU CHO MAY QUAN LY TOILET
if (sReaNo_VS1.IndexOf(REA_NO, 0) < 0) //(REA_NO != sReaNo_VS1 && REA_NO != sReaNo_VS2)
{
sql = "Insert into FILC01A(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
sql += "N'" + CRD_DT + CRD_TM_02 + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + T_String.IsNullTo00(CRD_TM_02) + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "')";
}
else
{
sql = "Insert into FILC01A_VS(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
sql += "N'" + CRD_DT + CRD_TM_02 + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + T_String.IsNullTo00(CRD_TM_02) + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "')";
}
// sql="Insert into FILC01A(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
// sql+="N'"+CRD_DT+CRD_TM_02+"',N'"+EMP_ID+"','"+dt+"',N'"+PublicFunction.A_UserID+"',";
// sql+="'"+CRD_DT+"',"+T_String.IsNullTo00(CRD_TM_02)+",";
// sql+="N'"+CRD_NO+"',N'"+REA_NO+"',";
// sql+="N'"+ S_sDir + "\\" + _lbxFile.Items[i].ToString() +"')";
try
{
PublicFunction.SQL_Execute(sql, SqlCon);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
}
}
}
catch (Exception ex)
{
// if (bContinue == false)
// {
if (MessageBox.Show(ex.Message + "\n\rText File: " + _lbxFile.Items[i].ToString() +
", Bookmarks: " + Convert.ToString(S_dbBook + 1) +
"\r\nContinue!", "Error!", System.Windows.Forms.MessageBoxButtons.YesNo) == DialogResult.No)
{
stop = true;
}
// bContinue = true;
// }
}
_lbxRecord.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM + " " + CRD_TM_02);
_lbxRecord.SelectedIndex = _lbxRecord.Items.Count - 1;
// _lbNumRec.Text = (ad_Rs.Bookmark ) + "/" + ad_Rs.RecordCount.ToString();
// _lbNumRec.Text = (S_dbBook.ToString()+1) + "/" + ad_Rs.RecordCount.ToString();
S_dbBook++;
ad_Rs.MoveNext();
//Tao khoa lam cho chuyen du lieu chay cham
//LAY NGAY KHOA DUOI SQL
if (stype.ToUpper() == "AS")
{
if (DateTime.Now >= DateTime.Parse(keyD))
{
Thread.Sleep(500 * iphut);
}
}
}
}
}
if (_lbxFile.Items.Count > 0)
{
string sql = "update GP_SYS_READER set RCD_FG=" + S_dbBook + ", FIL_NM=N'" + S_sDir + "\\" + _lbxFile.Items[_lbxFile.Items.Count - 1] + "' where SEQ_NO=N'" + _iSEQ_NO + "'";
PublicFunction.SQL_Execute(sql, SqlCon);
}
_bFin = true;
SqlCon.Close();
ad_Rs.Close();
ADODB_Close(ad_Con);
}