public frm_Queries(frm_menu mm)
{
InitializeComponent();
mainMenu = mm;
FillComboBx();
dtp_from.Format = DateTimePickerFormat.Custom;
dtp_to.Format = DateTimePickerFormat.Custom;
dtp_from.CustomFormat = "yyyy/MM/dd";
dtp_to.CustomFormat = "yyyy/MM/dd";
}
public frm_ndtStatus(frm_menu mm)
{
InitializeComponent();
dgv_RT.SelectionMode = DataGridViewSelectionMode.CellSelect;
dgv_pwht.SelectionMode = DataGridViewSelectionMode.CellSelect;
dgv_pmi.SelectionMode = DataGridViewSelectionMode.CellSelect;
dgv_htb.SelectionMode = DataGridViewSelectionMode.CellSelect;
dgv_hta.SelectionMode = DataGridViewSelectionMode.CellSelect;
dgv_pt.SelectionMode = DataGridViewSelectionMode.CellSelect;
dgv_ft.SelectionMode = DataGridViewSelectionMode.CellSelect;
mainMenu = mm;
}
public frm_Reports(frm_menu mm)
{
InitializeComponent();
conStr = File.ReadAllText(@"..\ndtCon.cfg");
mainMenu = mm;
}
public static void CheckFiles(string ndtType, frm_menu updateLoading)
{
HashSet <string> dtpdf = new HashSet <string>();
useSQL.useSQL dbCon;
string conStr = File.ReadAllText(@"..\ndtCon.cfg");
DirectoryInfo d = null;
if (ndtType.Equals("RT"))
{
d = new DirectoryInfo(@"Z:\04 - Construction\04-Site Progress Report\ISOMETRICS\ABOVEGROUND\NDT\RT");
}
else if (ndtType.Equals("PWHT"))
{
d = new DirectoryInfo(@"Z:\04 - Construction\04-Site Progress Report\ISOMETRICS\ABOVEGROUND\NDT\PWHT");
}
else if (ndtType.Equals("HTA"))
{
d = new DirectoryInfo(@"Z:\04 - Construction\04-Site Progress Report\ISOMETRICS\ABOVEGROUND\NDT\HTA");
}
else if (ndtType.Equals("HTB"))
{
d = new DirectoryInfo(@"Z:\04 - Construction\04-Site Progress Report\ISOMETRICS\ABOVEGROUND\NDT\HTB");
}
else if (ndtType.Equals("PMI"))
{
d = new DirectoryInfo(@"Z:\04 - Construction\04-Site Progress Report\ISOMETRICS\ABOVEGROUND\NDT\PMI");
}
else if (ndtType.Equals("PT"))
{
d = new DirectoryInfo(@"Z:\04 - Construction\04-Site Progress Report\ISOMETRICS\ABOVEGROUND\NDT\PT");
}
else if (ndtType.Equals("FT") || ndtType.Equals("FERRIT"))
{
d = new DirectoryInfo(@"Z:\04 - Construction\04-Site Progress Report\ISOMETRICS\ABOVEGROUND\NDT\Ferrit");
}
else if (ndtType.Equals("DWR"))
{
d = new DirectoryInfo(@"Z:\04 - Construction\04-Site Progress Report\ISOMETRICS\ABOVEGROUND\WeldReport");
}
FileInfo[] Files = d.GetFiles("*.pdf");
int filectr = 0;
foreach (FileInfo file in Files)
{
filectr++;
dtpdf.Add(Path.GetFileNameWithoutExtension(file.Name));
updateLoading.ChangeUpperLabel("TOTAL PDF FILES:" + filectr);
}
DataTable dtDBReport = new DataTable();
using (dbCon = new useSQL.useSQL(conStr))
{
if (ndtType.Equals("RT"))
{
dtDBReport = dbCon.PerformQuery("select distinct RTRIM(rtnumber1) as [reportnum],unit,service,line, train, JointNumber, rtdate1,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] "
+ " from Joints WITH(NOLOCK) WHERE RTNUMBER1 <> '' union select distinct RTRIM(RTnumber2) "
+ ",unit,service,line, train, JointNumber, rtdate2,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE RTNUMBER2 <> '' "
+ " union select distinct RTRIM(RTnumber3),unit,service,line, train, JointNumber,rtdate3,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WHERE RTNUMBER3 <> '' union select distinct RTRIM(reshoot) "
+ ",unit,service,line, train, JointNumber,reshootdate,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE RESHOOT <> '' union "
+ "select distinct RTRIM(reshoot2),unit,service,line, train, JointNumber,reshootdate2,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] "
+ "from Joints WITH(NOLOCK) WHERE RESHOOT2 <> ''");
}
else if (ndtType.Equals("PWHT"))
{
dtDBReport = dbCon.PerformQuery("select distinct RTRIM(PWHT1) as [reportnum],unit,service,line, train, JointNumber,pwhtdate1,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE PWHT1 <> '' union select distinct RTRIM(PWHT2)"
+ ",unit,service,line, train, JointNumber,pwhtdate2,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE PWHT2 <> '' union select distinct RTRIM(PWHT3)"
+ ",unit,service,line, train, JointNumber,pwhtdate3,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE PWHT3 <>''");
}
else if (ndtType.Equals("HTA"))
{
dtDBReport = dbCon.PerformQuery("select distinct RTRIM(HT1) as [reportnum],unit,service,line, train, JointNumber,htdate1,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE HT1 <> '' union select distinct RTRIM(HT2)"
+ ",unit,service,line, train, JointNumber,htdate2,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE HT2 <> '' union select distinct RTRIM(HT3),unit,service,line, train, JointNumber,htdate3,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] "
+ " from Joints WITH(NOLOCK) WHERE HT3 <>''");
}
else if (ndtType.Equals("HTB"))
{
dtDBReport = dbCon.PerformQuery("select distinct RTRIM(HTB) as [reportnum],unit,service,line, train, JointNumber,htbdate,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE HTB <> '' union select distinct RTRIM(HTB2)"
+ ",unit,service,line, train, JointNumber,htb2date,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE HTB2 <> '' union select distinct RTRIM(HTB3),unit,service,line, train, JointNumber,htb3date,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] "
+ " from Joints WITH(NOLOCK) WHERE HTB3 <>''");
}
else if (ndtType.Equals("PMI"))
{
dtDBReport = dbCon.PerformQuery("select distinct RTRIM(PMINUMBER) as [reportnum],unit,service,line, train, JointNumber, pmidate,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE PMINUMBER <> ''");
}
else if (ndtType.Equals("PT"))
{
dtDBReport = dbCon.PerformQuery("select distinct RTRIM(PTNUMBER) as [reportnum],unit,service,line, train, JointNumber,ptdate,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE PTNUMBER <> ''");
}
else if (ndtType.Equals("FT") || ndtType.Equals("FERRIT"))
{
dtDBReport = dbCon.PerformQuery("select distinct RTRIM(FERRIT) as [reportnum],unit,service,line, train, JointNumber,ferritdate,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE FERRIT <> '' union select distinct RTRIM(FERRIT2)"
+ ",unit,service,line, train, JointNumber,ferrit2date,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] from Joints WITH(NOLOCK) WHERE FERRIT2 <> '' union select distinct RTRIM(FERRIT3),unit,service,line, train, JointNumber,ferrit3date,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE]"
+ " from Joints WITH(NOLOCK) WHERE FERRIT3 <> ''");
}
else if (ndtType.Equals("DWR"))
{
dtDBReport = dbCon.PerformQuery("SELECT DISTINCT WeldingReportNumber AS [reportnum],UNIT,SERVICE,LINE,TRAIN, JOINTNUMBER, dateofweld,case when active = 1 then 'ACTIVE' else 'CANCELED' end as [ACTIVE] FROM JOINTS"
+ " WHERE"
+ " WeldingReportNumber <> ''"
+ " AND "
+ " Active =1"
+ " AND JointType NOT IN ('FJ','TH')");
}
}
var hashSet = new HashSet <string[]>(DTtoList(dtDBReport));
var pcaReports = new HashSet <string>(dtDBReport.AsEnumerable().Select(x => x.Field <string>("reportnum")));
DataTable dtresult1 = new DataTable();
dtresult1.Columns.Add("ReportNumber");
dtresult1.Columns.Add("Result");
string[] strResult1 = null;
var hashReports = hashSet.First();
int pdfreportCount = 0;
if (!ndtType.Equals("DWR"))
{
foreach (string c in dtpdf)
{
updateLoading.ChangeLowerLabel(pdfreportCount++, dtpdf.Count(), "PDF FILE CHECKING: ");
if (pcaReports.Contains(c))
{
strResult1 = new string[] { c, "REPORT UPDATED IN PCA" };
}
else
{
strResult1 = new string[] { c, "NOT UPDATED" };
}
dtresult1.NewRow();
dtresult1.Rows.Add(strResult1);
}
}
DataTable dtresult2 = new DataTable();
dtresult2.Columns.Add("UNIT");
dtresult2.Columns.Add("SERVICE");
dtresult2.Columns.Add("LINE");
dtresult2.Columns.Add("TRAIN");
dtresult2.Columns.Add("JOINTNUMBER");
dtresult2.Columns.Add("ReportNumber");
dtresult2.Columns.Add("ReportDate");
dtresult2.Columns.Add("Joint Active");
dtresult2.Columns.Add("Result");
string[] strResult2 = null;
int dbReportCount = 0;
int totalrepcount = hashSet.Count();
foreach (string[] c in hashSet)
{
dbReportCount++;
//updateLoading.ChangeLowerLabel(dbReportCount, totalrepcount, "PCA JOINT REPORTS CHECKING: ");
//updateLoading.ChangeUpperLabel("PAKPAKPAK LABAN LNG BES:" + dbReportCount);
if (Regex.IsMatch(c[0], @"\s"))
{
if (dtpdf.Contains(c[0].Trim()))
{
if (!ndtType.Equals("DWR"))
{
strResult2 = new string[] { c[1], c[2], c[3], c[4], c[5], c[0], c[6], c[7], "REPORT EXISTING IN MAIN FOLDER BUT UPDATED WITH SPACES" };
}
}
else
{
strResult2 = new string[] { c[1], c[2], c[3], c[4], c[5], c[0], c[6], c[7], "REPORT MISSING IN MAIN FOLDER" };
}
}
else
{
if (dtpdf.Contains(c[0]))
{
if (!ndtType.Equals("DWR"))
{
strResult2 = new string[] { c[1], c[2], c[3], c[4], c[5], c[0], c[6], c[7], "REPORT EXISTING IN MAIN FOLDER" };
}
}
else
{
strResult2 = new string[] { c[1], c[2], c[3], c[4], c[5], c[0], c[6], c[7], "REPORT MISSING IN MAIN FOLDER" };
}
}
if (strResult2 != null)
{
dtresult2.NewRow();
dtresult2.Rows.Add(strResult2);
}
}
string fn = null;
if (ndtType.Equals("RT"))
{
fn = " RT CHECK FILE RESULT.xlsx";
}
else if (ndtType.Equals("PWHT"))
{
fn = " PWHT CHECK FILE RESULT.xlsx";
}
else if (ndtType.Equals("HTA"))
{
fn = " HTA CHECK FILE RESULT.xlsx";
}
else if (ndtType.Equals("HTB"))
{
fn = " HTB CHECK FILE RESULT.xlsx";
}
else if (ndtType.Equals("PMI"))
{
fn = " PMI CHECK FILE RESULT.xlsx";
}
else if (ndtType.Equals("PT"))
{
fn = " PT CHECK FILE RESULT.xlsx";
}
else if (ndtType.Equals("FT") || ndtType.Equals("FERRIT"))
{
fn = " FT CHECK FILE RESULT.xlsx";
}
else if (ndtType.Equals("DWR"))
{
fn = " DWR CHECK FILE RESULT.csv";
}
if (!ndtType.Equals("DWR"))
{
try
{
ExportExcel(dtresult1, dtresult2, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + DateTime.Now.ToString("dd-MM-yyyy") + fn);
MetroMessageBox.Show(updateLoading, "", "RESULT SUCCESSFULLY IMPORTED! FILE LOCATED IN YOUR DESKTOP.", MessageBoxButtons.OK, MessageBoxIcon.Question, 150);
}
catch (Exception ex)
{
MetroMessageBox.Show(updateLoading, "IMPORT FAILED", "There is something wrong. " + ex.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Hand, 150);
}
}
else
{
ExportExcel(dtresult2, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + DateTime.Now.ToString("dd-MM-yyyy") + fn);
}
}
