Esempio n. 1
0
        private void tsbtnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable tmptable = new DataTable();

                tmptable = ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in tmptable.Rows)
                {
                    string staffid = row.ItemArray[0].ToString();
                    staffid = staffid.ToLower();

                    string name    = staffid.StartsWith("hk") ? AdUtil.getUsernameByUserId(staffid, "kmhk.local") : AdUtil.getUsernameByUserId(staffid, "kmas.local");
                    string company = staffid.StartsWith("hk") ? "KDTHK" : staffid.StartsWith("as") ? "KDAS" : "KDHK";

                    string query = string.Format("if exists (select * from TB_STAFF where st_staffid = '{0}')" +
                                                 " update TB_STAFF set st_name = N'{1}', st_company = '{2}' where st_staffid = '{0}' else" +
                                                 " insert into TB_STAFF (st_staffid, st_name, st_company) values ('{0}', N'{1}', '{2}')", staffid, name, company);
                    DataServiceEducation.GetInstance().ExecuteNonQuery(query);
                }
            }
            MessageBox.Show("Record has been saved.");
        }
Esempio n. 2
0
        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);
                foreach (DataRow row in table.Rows)
                {
                    string rgno     = row.ItemArray[0].ToString();
                    string content  = row.ItemArray[1].ToString();
                    string currency = row.ItemArray[2].ToString();
                    string amount   = row.ItemArray[3].ToString();
                    string rate     = row.ItemArray[4].ToString();
                    string hkd      = row.ItemArray[5].ToString();
                    string regdate  = (Convert.ToDateTime(row.ItemArray[6]).ToString("yyyy/MM/dd"));
                    string expired  = (Convert.ToDateTime(row.ItemArray[7]).ToString("yyyy/MM/dd"));
                    string r3date   = (Convert.ToDateTime(row.ItemArray[8]).ToString("yyyy/MM/dd"));
                    string balance  = row.ItemArray[9].ToString();

                    string query = string.Format("insert into TB_MASTER_RINGI (mr_no, mr_content, mr_currency" +
                                                 ", mr_amount, mr_rate, mr_amounthkd, mr_regdate, mr_expired, mr_confirmdate, mr_balance)" +
                                                 " values ('{0}', N'{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}')", rgno, content, currency, amount,
                                                 rate, hkd, regdate, expired, r3date, balance);
                    DataService.GetInstance().ExecuteNonQuery(query);
                }
            }
        }
Esempio n. 3
0
        private void btnKdas_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string id = row.ItemArray[0].ToString().Trim();

                    string name = AdUtil.getUsernameByUserId(id, "kmas.local");

                    string dept = row.ItemArray[2].ToString().Trim();

                    string sect = row.ItemArray[3].ToString().Trim();

                    string div = row.ItemArray[4].ToString().Trim();

                    string query = string.Format("insert into TB_USER_AS (u_staffid, u_name, u_dept, u_sect, u_div) values ('{0}', N'{1}', '{2}', N'{3}', N'{4}')", id, name, dept, sect, div);
                    DataServiceM.GetInstance().ExecuteNonQuery(query);
                }
            }
        }
Esempio n. 4
0
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string question   = row.ItemArray[0].ToString().Trim();
                    string category   = row.ItemArray[1].ToString().Trim();
                    string questionjp = row.ItemArray[2].ToString().Trim();
                    string categoryjp = row.ItemArray[3].ToString().Trim();
                    string answer     = row.ItemArray[4].ToString().Trim();
                    string answerjp   = row.ItemArray[5].ToString().Trim();
                    string correct    = row.ItemArray[6].ToString().Trim();
                    string id         = row.ItemArray[7].ToString().Trim();

                    string query = string.Format("if not exists (select * from TB_IT_EDU_QUESTION where q_question = N'{0}') insert into TB_IT_EDU_QUESTION (q_question, q_questionjp, q_type, q_typejp)" +
                                                 " values (N'{0}', N'{1}', N'{2}', N'{3}')", question, category, questionjp, categoryjp);
                    DataServiceEdu.GetInstance().ExecuteNonQuery(query);

                    string text = string.Format("insert into TB_IT_EDU_ANSWER (a_answer, a_answerjp, a_correct, a_questionid) values (N'{0}', N'{1}', '{2}', '{3}')", answer, answerjp, correct, id);
                    DataServiceEdu.GetInstance().ExecuteNonQuery(text);
                }
            }
        }
        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string request    = row.ItemArray[0].ToString().Trim();
                    string applicant  = row.ItemArray[1].ToString().Trim();
                    string type       = row.ItemArray[2].ToString().Trim();
                    string chaseno    = row.ItemArray[3].ToString().Trim();
                    string pdfid      = row.ItemArray[4].ToString().Trim();
                    string partno     = row.ItemArray[5].ToString().Trim();
                    string fixedasset = row.ItemArray[6].ToString().Trim();
                    string tmpfa      = row.ItemArray[7].ToString().Trim();
                    string desc       = row.ItemArray[8].ToString().Trim();
                    string mpa        = row.ItemArray[9].ToString().Trim();
                    string assetclass = row.ItemArray[10].ToString().Trim();
                    string vendor     = row.ItemArray[11].ToString().Trim();
                    if (vendor.Length == 9)
                    {
                        vendor = "0" + vendor;
                    }
                    string mould     = row.ItemArray[12].ToString().Trim();
                    string ringi     = row.ItemArray[13].ToString().Trim();
                    string model     = row.ItemArray[14].ToString().Trim();
                    string currency  = row.ItemArray[15].ToString().Trim();
                    string amount    = row.ItemArray[16].ToString().Trim();
                    string assetdesc = vendor + "Test Vendor";

                    string attachment = @"\\kdthk-dm1\moss$\cm\fixedasset\" + pdfid + ".pdf";

                    string query = string.Format("insert into TB_FA_APPROVAL (f_request, f_applicant, f_type, f_chaseno, f_pdfid, f_status, f_partno, f_fixedasset, f_desc, f_mpa, f_assetclass" +
                                                 ", f_vendor, f_attachment, f_mould, f_costcenter, f_resp, f_location, f_ringi, f_model, f_currency, f_amount, f_assetdesc) values ('{0}', N'{1}', '{2}'" +
                                                 ", '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}')", request, applicant, "Acquisition", chaseno, pdfid,
                                                 "Finish", partno, fixedasset, desc, mpa, assetclass, vendor, attachment, mould, "1404000029", "1404000029", "1404000000", ringi, model, currency, amount, assetdesc);

                    DataService.GetInstance().ExecuteNonQuery(query);

                    if (tmpfa != "")
                    {
                        string text = string.Format("insert into TB_FA_APPROVAL (f_request, f_applicant, f_type, f_chaseno, f_pdfid, f_status, f_partno, f_fixedasset, f_desc, f_mpa, f_assetclass" +
                                                    ", f_vendor, f_attachment, f_mould, f_costcenter, f_resp, f_location, f_ringi, f_model, f_currency, f_amount, f_assetdesc) values ('{0}', N'{1}', '{2}'" +
                                                    ", '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}')", request, applicant, "Acquisition", chaseno, pdfid,
                                                    "Finish", partno, tmpfa, desc, mpa, assetclass, vendor, attachment, mould, "1404000029", "1404000029", "1404000000", ringi, model, currency, amount, assetdesc);

                        DataService.GetInstance().ExecuteNonQuery(text);
                    }
                }
            }
        }
Esempio n. 6
0
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                DataTable errorTable = new DataTable();
                errorTable.Columns.Add("error");

                foreach (DataRow row in table.Rows)
                {
                    //string owner = row.ItemArray[0].ToString().Trim();
                    string path = row.ItemArray[0].ToString().Trim();

                    FileInfo info = new FileInfo(path);

                    try
                    {
                        using (var impersonation = new ImpersonatedUser("itadmin", "kmhk.local", "Cv%^yHfv"))
                        {
                            FileSecurity fs = info.GetAccessControl();
                            AuthorizationRuleCollection rules = fs.GetAccessRules(true, true, typeof(NTAccount));

                            fs.SetAccessRuleProtection(true, false);
                            //if (owner == "Ho Kin Hang(何健恒,Ken)")
                            //fs.AddAccessRule(new FileSystemAccessRule(@"kmas\as1600055", FileSystemRights.FullControl, AccessControlType.Allow));
                            //else
                            fs.AddAccessRule(new FileSystemAccessRule(@"kmas\as1600048", FileSystemRights.FullControl, AccessControlType.Allow));

                            File.SetAccessControl(path, fs);
                        }
                    }
                    catch
                    {
                        errorTable.Rows.Add(path);
                        continue;
                    }
                }

                if (errorTable.Rows.Count > 0)
                {
                    ExportCsvUtil.ExportCsv(errorTable, "", "");
                }
            }
        }
Esempio n. 7
0
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            DataTable errorTable = new DataTable();

            errorTable.Columns.Add("missed");

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                using (var impersonation = new ImpersonatedUser("itadmin", "kmhk.local", "Ed@a*9kj"))
                {
                    foreach (DataRow row in table.Rows)
                    {
                        string file = row.ItemArray[0].ToString().Trim();
                        string user = row.ItemArray[1].ToString().Trim();
                        string path = row.ItemArray[2].ToString().Trim();

                        if (!File.Exists(path))
                        {
                            errorTable.Rows.Add(path);
                            continue;
                        }

                        string filename  = Path.GetFileName(path);
                        string newPath   = path.Replace(@"\\kdthk-dm1", "");
                        string directory = @"\\kdthk-dm1\dvdstorage$" + newPath.Substring(0, newPath.LastIndexOf(@"\") + 1);

                        if (!Directory.Exists(directory))
                        {
                            Directory.CreateDirectory(directory);
                        }

                        File.Copy(path, directory + filename, true);
                    }
                }

                if (errorTable.Rows.Count > 0)
                {
                    ExportCsvUtil.ExportCsv(errorTable, "", "missed");
                }
            }
        }
Esempio n. 8
0
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string id     = row.ItemArray[0].ToString().Trim();
                    string partno = row.ItemArray[2].ToString().Trim();

                    string query = string.Format("update TB_FA_APPROVAL set f_partno = '{0}' where f_id = '{1}'", partno, id);
                    DataService.GetInstance().ExecuteNonQuery(query);
                }
            }
        }
Esempio n. 9
0
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string staff = row.ItemArray[0].ToString().Trim();
                    string path  = row.ItemArray[1].ToString().Trim();
                    string name  = row.ItemArray[2].ToString().Trim();
                    string desc  = row.ItemArray[3].ToString().Trim();

                    string query = string.Format("insert into TB_APPLICATION (f_category, f_staff, f_name, f_path, f_description) values ('Application', N'{0}', N'{1}', N'{2}', N'{3}')", staff, name, path, desc);
                    DataService.GetInstance().ExecuteNonQuery(query);
                }
            }
        }
Esempio n. 10
0
        private void dgvDisc_DoubleClick(object sender, EventArgs e)
        {
            dgvDiscView.Rows.Clear();

            pnlDisc.Enabled = true;

            string discNo   = dgvDisc.SelectedRows[0].Cells[0].Value.ToString();
            string filePath = dgvDisc.SelectedRows[0].Cells[1].Value.ToString();

            lblDiscNo.Text = "Disc No. : " + discNo;

            DiscTable = ImportExcel2007.TranslateToTable(filePath);

            foreach (DataRow dr in DiscTable.Rows)
            {
                string fileName = dr.ItemArray[1].ToString();

                dgvDiscView.Rows.Add(fileName);
            }
        }
Esempio n. 11
0
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            double size = 0;

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string[] sizes = { "B", "KB", "MB", "GB" };

                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                using (var impersonation = new ImpersonatedUser("itadmin", "kmhk.local", "Ed@a*9kj"))
                {
                    foreach (DataRow row in table.Rows)
                    {
                        //string file = row.ItemArray[0].ToString().Trim();
                        //string user = row.ItemArray[1].ToString().Trim();
                        string path = row.ItemArray[2].ToString().Trim();

                        if (!File.Exists(path))
                        {
                            continue;
                        }

                        double len = new FileInfo(path).Length;
                        size += len;
                    }
                }

                int order = 0;
                while (size >= 1024 && order + 1 < sizes.Length)
                {
                    order++;
                    size = size / 1024;
                }

                MessageBox.Show(string.Format("{0:0.##} {1}", size, sizes[order]));
            }
        }
Esempio n. 12
0
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable tb1 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDTHK");
                foreach (DataRow row in tb1.Rows)
                {
                    string id   = row.ItemArray[0].ToString().Trim();
                    string name = row.ItemArray[1].ToString().Trim();

                    string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDTHK");
                    DataServiceEdu.GetInstance().ExecuteNonQuery(query);
                }

                DataTable tb2 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDHK");
                foreach (DataRow row in tb2.Rows)
                {
                    string id   = row.ItemArray[0].ToString().Trim();
                    string name = row.ItemArray[1].ToString().Trim();

                    string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDHK");
                    DataServiceEdu.GetInstance().ExecuteNonQuery(query);
                }

                DataTable tb3 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDAS");
                foreach (DataRow row in tb3.Rows)
                {
                    string id   = row.ItemArray[0].ToString().Trim();
                    string name = row.ItemArray[1].ToString().Trim();

                    string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDAS");
                    DataServiceEdu.GetInstance().ExecuteNonQuery(query);
                }
            }
        }
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith("xlsx") ? ImportExcel2007.TranslateToTable(ofd.FileName) : ImportExcel2003.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string model = row.ItemArray[0].ToString().Trim();
                    string code = row.ItemArray[1].ToString().Trim();

                    string query = string.Format("if not exists (select * from tb_model where m_model = '{0}' and m_code = '{1}') insert into tb_model (m_model, m_code) values ('{0}', '{1}')", model, code);
                    DataService.GetInstance().ExecuteNonQuery(query);
                }
                MessageBox.Show("Record has been uploaded.");
            }

            if (cbSearch.SelectedIndex == 0)
                SearchData("");
            else
                SearchData(txtSearch.Text.Trim());
        }
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith("xlsx") ? ImportExcel2007.TranslateToTable(ofd.FileName) : ImportExcel2003.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string type   = row.ItemArray[0].ToString().Trim();
                    string mould  = row.ItemArray[1].ToString().Trim();
                    string partno = row.ItemArray[2].ToString().Trim();
                    string rev    = row.ItemArray[3].ToString().Trim();
                    string asset  = row.ItemArray[4].ToString().Trim();
                    string vendor = row.ItemArray[5].ToString().Trim();
                    if (vendor.Length == 9)
                    {
                        vendor = "0" + vendor;
                    }

                    string vendorname = row.ItemArray[6].ToString().Trim();
                    if (vendorname.Contains("'"))
                    {
                        vendorname = vendorname.Replace("'", "''");
                    }

                    string p2003no = row.ItemArray[7].ToString().Trim();
                    if (p2003no.Contains("'"))
                    {
                        p2003no = p2003no.Replace("'", "''");
                    }

                    string p2003ans = row.ItemArray[8].ToString().Trim();

                    try
                    {
                        p2003ans = ImportExcel2007.ParseDateTime(p2003ans).ToString("yyyy/MM/dd");
                        if (p2003ans.StartsWith("00"))
                        {
                            p2003ans = row.ItemArray[8].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    if (p2003ans.Contains("'"))
                    {
                        p2003ans = p2003ans.Replace("'", "''");
                    }

                    string p2003result = row.ItemArray[9].ToString().Trim();
                    try
                    {
                        p2003result = ImportExcel2007.ParseDateTime(p2003result).ToString("yyyy/MM/dd");
                        if (p2003result.StartsWith("00"))
                        {
                            p2003result = row.ItemArray[9].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    if (p2003result.Contains("'"))
                    {
                        p2003result = p2003result.Replace("'", "''");
                    }

                    string p2003date = row.ItemArray[10].ToString().Trim();
                    try
                    {
                        p2003date = ImportExcel2007.ParseDateTime(p2003date).ToString("yyyy/MM/dd");
                        if (p2003date.StartsWith("00"))
                        {
                            p2003date = row.ItemArray[10].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string p2004no = row.ItemArray[11].ToString().Trim();
                    if (p2004no.Contains("'"))
                    {
                        p2004no = p2004no.Replace("'", "''");
                    }
                    string p2004ans = row.ItemArray[12].ToString().Trim();
                    try
                    {
                        p2004ans = ImportExcel2007.ParseDateTime(p2004ans).ToString("yyyy/MM/dd");
                        if (p2004ans.StartsWith("00"))
                        {
                            p2004ans = row.ItemArray[12].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    if (p2004ans.Contains("'"))
                    {
                        p2004ans = p2004ans.Replace("'", "''");
                    }

                    string p2004result = row.ItemArray[13].ToString().Trim();
                    try
                    {
                        p2004result = ImportExcel2007.ParseDateTime(p2004result).ToString("yyyy/MM/dd");
                        if (p2004result.StartsWith("00"))
                        {
                            p2004result = row.ItemArray[13].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    if (p2004result.Contains("'"))
                    {
                        p2004result = p2004result.Replace("'", "''");
                    }

                    string p2004date = row.ItemArray[14].ToString().Trim();
                    try
                    {
                        p2004date = ImportExcel2007.ParseDateTime(p2004date).ToString("yyyy/MM/dd");
                        if (p2004date.StartsWith("00"))
                        {
                            p2004date = row.ItemArray[14].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string kdcno = row.ItemArray[15].ToString().Trim();
                    if (kdcno.Contains("'"))
                    {
                        kdcno = kdcno.Replace("'", "''");
                    }

                    string kdcdate = row.ItemArray[16].ToString().Trim();
                    try
                    {
                        kdcdate = ImportExcel2007.ParseDateTime(kdcdate).ToString("yyyy/MM/dd");
                        if (kdcdate.StartsWith("00"))
                        {
                            kdcdate = row.ItemArray[16].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string kdcrps = row.ItemArray[17].ToString().Trim();
                    try
                    {
                        kdcrps = ImportExcel2007.ParseDateTime(kdcrps).ToString("yyyy/MM/dd");
                        if (kdcrps.StartsWith("00"))
                        {
                            kdcrps = row.ItemArray[17].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    if (kdcrps.Contains("'"))
                    {
                        kdcrps = kdcrps.Replace("'", "''");
                    }

                    string kdcans = row.ItemArray[18].ToString().Trim();
                    try
                    {
                        kdcans = ImportExcel2007.ParseDateTime(kdcans).ToString("yyyy/MM/dd");
                        if (kdcans.StartsWith("00"))
                        {
                            kdcans = row.ItemArray[18].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    if (kdcans.Contains("'"))
                    {
                        kdcans = kdcans.Replace("'", "''");
                    }

                    string kdcresult = row.ItemArray[19].ToString().Trim();
                    try
                    {
                        kdcresult = ImportExcel2007.ParseDateTime(kdcresult).ToString("yyyy/MM/dd");
                        if (kdcresult.StartsWith("00"))
                        {
                            kdcresult = row.ItemArray[19].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    if (kdcresult.Contains("'"))
                    {
                        kdcresult.Replace("'", "''");
                    }

                    string kdcupdate = row.ItemArray[20].ToString().Trim();
                    try
                    {
                        kdcupdate = ImportExcel2007.ParseDateTime(kdcupdate).ToString("yyyy/MM/dd");
                        if (kdcupdate.StartsWith("00"))
                        {
                            kdcupdate = row.ItemArray[20].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }

                    string assetdesc = row.ItemArray[21].ToString().Trim();
                    if (assetdesc.Contains("'"))
                    {
                        assetdesc = assetdesc.Replace("'", "''");
                    }

                    string capdate = row.ItemArray[22].ToString().Trim();
                    try
                    {
                        capdate = ImportExcel2007.ParseDateTime(capdate).ToString("yyyy/MM/dd");
                        if (capdate.StartsWith("00"))
                        {
                            capdate = row.ItemArray[22].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string acquis  = row.ItemArray[23].ToString().Trim();
                    string accum   = row.ItemArray[24].ToString().Trim();
                    string closing = row.ItemArray[25].ToString().Trim();
                    try
                    {
                        closing = ImportExcel2007.ParseDateTime(closing).ToString("yyyy/MM/dd");
                        if (closing.StartsWith("00"))
                        {
                            closing = row.ItemArray[25].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string bookval = row.ItemArray[26].ToString().Trim();
                    string year    = row.ItemArray[27].ToString().Trim();
                    try
                    {
                        year = ImportExcel2007.ParseDateTime(year).ToString("yyyy/MM/dd");
                        if (year.StartsWith("00"))
                        {
                            year = row.ItemArray[27].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string bookval2 = row.ItemArray[28].ToString().Trim();
                    string ringi    = row.ItemArray[29].ToString().Trim();
                    string reportno = row.ItemArray[30].ToString().Trim();
                    if (reportno.Contains("''"))
                    {
                        reportno = reportno.Replace("'", "''");
                    }
                    string reportissued = row.ItemArray[31].ToString().Trim();
                    try
                    {
                        reportissued = ImportExcel2007.ParseDateTime(reportissued).ToString("yyyy/MM/dd");
                        if (reportissued.StartsWith("00"))
                        {
                            reportissued = row.ItemArray[31].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string reportdate = row.ItemArray[32].ToString().Trim();
                    try
                    {
                        reportdate = ImportExcel2007.ParseDateTime(reportdate).ToString("yyyy/MM/dd");
                        if (reportdate.StartsWith("00"))
                        {
                            reportdate = row.ItemArray[32].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string vendorans     = row.ItemArray[33].ToString().Trim();
                    string assetdisposal = row.ItemArray[34].ToString().Trim();
                    try
                    {
                        assetdisposal = ImportExcel2007.ParseDateTime(assetdisposal).ToString("yyyy/MM/dd");
                        if (assetdisposal.StartsWith("00"))
                        {
                            assetdisposal = row.ItemArray[34].ToString().Trim();
                        }
                    }
                    catch
                    {
                    }
                    string remarks = row.ItemArray[35].ToString().Trim();
                    string status  = row.ItemArray[36].ToString().Trim();

                    if (closing.Contains("'"))
                    {
                        closing = closing.Replace("'", "''");
                    }
                    if (year.Contains("'"))
                    {
                        year = year.Replace("'", "''");
                    }

                    string query = string.Format("if not exists (select * from tb_disposaldetail where dd_mould = '{2}' and dd_partno = '{3}' and dd_asset = '{5}')" +
                                                 " insert into tb_disposaldetail (dd_type, dd_status, dd_mould, dd_partno, dd_rev, dd_asset, dd_vendor, dd_vendorname" +
                                                 ", dd_2003no, dd_2003answer, dd_2003result, dd_2003updated, dd_2004no, dd_2004answer, dd_2004result, dd_2004updated" +
                                                 ", dd_kdcno, dd_kdcissued, dd_kdcrps, dd_kdcanswer, dd_kdcresult, dd_kdcupdated, dd_assetdesc, dd_capdate, dd_acquishkd" +
                                                 ", dd_accumhkd, dd_closing, dd_bookhkd, dd_fy, dd_bookhkd2, dd_disposalringi, dd_reportno, dd_reportissued, dd_reportreceived" +
                                                 ", dd_vendorresult, dd_fadisposaldate, dd_disposalremarks) values (N'{0}', N'{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}'" +
                                                 ", '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}', '{24}', '{25}', '{26}', '{27}', '{28}', '{29}', '{30}'" +
                                                 ", N'{31}', '{32}', '{33}', '{34}', '{35}', N'{36}') else update tb_disposaldetail set dd_type = N'{0}', dd_rev = '{4}', dd_asset = '{5}', dd_vendor = '{6}'" +
                                                 ", dd_vendorname = N'{7}', dd_2003no = '{8}', dd_2003answer = '{9}', dd_2003result = '{10}', dd_2003updated = '{11}', dd_2004no = '{12}'" +
                                                 ", dd_2004answer = '{13}', dd_2004result = '{14}', dd_2004updated = '{15}', dd_kdcno = '{16}', dd_kdcissued = '{17}', dd_kdcrps = '{18}'" +
                                                 ", dd_kdcanswer = '{19}', dd_kdcresult = '{20}', dd_kdcupdated = '{21}', dd_assetdesc = '{22}', dd_capdate = '{23}', dd_acquishkd = '{24}'" +
                                                 ", dd_accumhkd = '{25}', dd_closing = '{26}', dd_bookhkd = '{27}', dd_fy = '{28}', dd_bookhkd2 = '{29}', dd_disposalringi = '{30}', dd_reportno = N'{31}'" +
                                                 ", dd_reportissued = '{32}', dd_reportreceived = '{33}', dd_vendorresult = '{34}', dd_fadisposaldate = '{35}', dd_disposalremarks = N'{36}', dd_status = N'{1}' where dd_mould = '{2}' and dd_partno = '{3}'"
                                                 , type, status, mould, partno, rev, asset, vendor, vendorname, p2003no, p2003ans,
                                                 p2003result, p2003date, p2004no, p2004ans, p2004result, p2004date, kdcno, kdcdate, kdcrps, kdcans, kdcresult, kdcupdate, assetdesc,
                                                 capdate, acquis, accum, closing, bookval, year, bookval2, ringi, reportno, reportissued, reportdate, vendorans, assetdisposal, remarks);

                    DataService.GetInstance().ExecuteNonQuery(query);

                    UpdateMouldData(mould, partno, reportno, status);
                }

                SearchData("");

                MessageBox.Show("Record has been uploaded.");
            }
        }
Esempio n. 15
0
        public static void UpdateData()
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel Files |*.xlsx";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string chaseno = row.ItemArray[0].ToString().Trim();
                    string partno  = row.ItemArray[1].ToString().Trim();
                    string rev     = row.ItemArray[2].ToString().Trim();
                    if (rev.Length == 1)
                    {
                        rev = "0" + rev;
                    }
                    string mould     = row.ItemArray[3].ToString().Trim();
                    string div       = row.ItemArray[4].ToString().Trim();
                    string type      = row.ItemArray[5].ToString().Trim();
                    string currency  = row.ItemArray[6].ToString().Trim();
                    string amount    = row.ItemArray[7].ToString().Trim();
                    string amounthkd = row.ItemArray[8].ToString().Trim();
                    string mpa       = row.ItemArray[9].ToString().Trim();
                    string fa        = row.ItemArray[10].ToString().Trim();
                    string tmpfa     = row.ItemArray[11].ToString().Trim();
                    string qty       = row.ItemArray[12].ToString().Trim();
                    string common    = row.ItemArray[13].ToString().Trim();
                    string itemtext  = row.ItemArray[14].ToString().Trim();
                    string request   = row.ItemArray[15].ToString().Trim();
                    string indate    = ImportExcel2007.ParseDateTime(row.ItemArray[16].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (indate == "0001/01/01")
                    {
                        indate = row.ItemArray[16].ToString().Trim();
                    }
                    string deliverydate = ImportExcel2007.ParseDateTime(row.ItemArray[17].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (deliverydate == "0001/01/01")
                    {
                        deliverydate = row.ItemArray[17].ToString().Trim();
                    }
                    string projecttext = row.ItemArray[18].ToString().Trim();
                    string model       = row.ItemArray[19].ToString().Trim();
                    string po          = row.ItemArray[20].ToString().Trim();
                    string porev       = row.ItemArray[21].ToString().Trim();
                    string issued      = ImportExcel2007.ParseDateTime(row.ItemArray[22].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (issued == "0001/01/01")
                    {
                        issued = row.ItemArray[22].ToString().Trim();
                    }
                    string category = row.ItemArray[23].ToString().Trim();
                    string ringi    = row.ItemArray[24].ToString().Trim();
                    string vendor   = row.ItemArray[25].ToString().Trim();
                    if (vendor.Length == 9)
                    {
                        vendor = "0" + vendor;
                    }
                    string mouldcode   = row.ItemArray[26].ToString().Trim();
                    string status      = row.ItemArray[27].ToString().Trim();
                    string owner       = row.ItemArray[28].ToString().Trim();
                    string oem         = row.ItemArray[29].ToString().Trim();
                    string remarks     = row.ItemArray[30].ToString().Trim();
                    string instockdate = ImportExcel2007.ParseDateTime(row.ItemArray[31].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (instockdate == "0001/01/01")
                    {
                        instockdate = row.ItemArray[31].ToString().Trim();
                    }
                    string instockdate50 = ImportExcel2007.ParseDateTime(row.ItemArray[32].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (instockdate50 == "0001/01/01")
                    {
                        instockdate50 = row.ItemArray[32].ToString().Trim();
                    }
                    string pgroup    = row.ItemArray[33].ToString().Trim();
                    string ac        = row.ItemArray[34].ToString().Trim();
                    string cc        = row.ItemArray[35].ToString().Trim();
                    string checkdate = ImportExcel2007.ParseDateTime(row.ItemArray[36].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (checkdate == "0001/01/01")
                    {
                        checkdate = row.ItemArray[36].ToString().Trim();
                    }
                    string checkdate2 = ImportExcel2007.ParseDateTime(row.ItemArray[37].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (checkdate2 == "0001/01/01")
                    {
                        checkdate2 = row.ItemArray[37].ToString().Trim();
                    }
                    string cav            = row.ItemArray[38].ToString().Trim();
                    string weight         = row.ItemArray[39].ToString().Trim();
                    string equipment      = row.ItemArray[40].ToString().Trim();
                    string shot           = row.ItemArray[41].ToString().Trim();
                    string vertical       = row.ItemArray[42].ToString().Trim();
                    string horizontal     = row.ItemArray[43].ToString().Trim();
                    string height         = row.ItemArray[44].ToString().Trim();
                    string ismodifiy      = row.ItemArray[45].ToString().Trim();
                    string instockremarks = row.ItemArray[46].ToString().Trim();
                    string collectdate    = ImportExcel2007.ParseDateTime(row.ItemArray[47].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (collectdate == "0001/01/01")
                    {
                        collectdate = row.ItemArray[47].ToString().Trim();
                    }
                    string pass   = row.ItemArray[48].ToString().Trim();
                    string pcs    = row.ItemArray[49].ToString().Trim();
                    string rmb50  = row.ItemArray[50].ToString().Trim();
                    string rmbtax = row.ItemArray[51].ToString().Trim();
                    string cndate = ImportExcel2007.ParseDateTime(row.ItemArray[52].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (cndate == "0001/01/01")
                    {
                        cndate = row.ItemArray[52].ToString().Trim();
                    }
                    string cnsenddate = ImportExcel2007.ParseDateTime(row.ItemArray[53].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (cnsenddate == "0001/01/01")
                    {
                        cnsenddate = row.ItemArray[53].ToString().Trim();
                    }

                    string query = string.Format("update tb_betamould set tm_itemcode = '{0}', tm_rev = '{1}', tm_mouldno = '{2}', tm_status = '{3}', tm_type = '{4}', tm_currency = '{5}', tm_amount = '{6}', tm_amounthkd = '{7}'" +
                                                 ", tm_mpa = '{8}', tm_fixedassetcode = '{9}', tm_tmpfixedassetcode = '{10}', tm_qty = '{11}', tm_common = '{12}', tm_itemtext = '{13}', tm_request = '{14}', tm_indate = '{15}', tm_deliverydate = '{16}'" +
                                                 ", tm_projecttext = '{17}', tm_model = '{18}', tm_po = '{19}', tm_porev = '{20}', tm_poissued = '{21}', tm_category = '{22}', tm_ringi_code = '{23}', tm_vendor_code = '{24}', tm_mouldcode_code = '{25}'" +
                                                 ", tm_st_code = '{26}', tm_owner = '{27}', tm_oemasset = '{28}', tm_rm = N'{29}', tm_instockdate = '{30}', tm_instockdate50 = '{31}', tm_group = '{32}', tm_accountcode = '{33}', tm_costcentre = '{34}'" +
                                                 ", tm_checkdate = '{35}', tm_checkdate2 = '{36}', tm_cav = '{37}', tm_weight = '{38}', tm_accessory = '{39}', tm_camera = '{40}', tm_vertical = '{41}', tm_horizontal = '{42}', tm_height = '{43}', tm_ismodify = '{44}'" +
                                                 ", tm_instockremarks = N'{45}', tm_collectdate = '{46}', tm_passremarks = N'{47}', tm_pcs = '{48}', tm_is50 = '{49}', tm_tax = '{50}', tm_cndatetime = '{51}', tm_cnsendtime = '{52}' where tm_chaseno = '{53}'",
                                                 partno, rev, mould, div, type, currency, amount, amounthkd, mpa, fa, tmpfa, qty, common, itemtext, request, indate, deliverydate, projecttext, model, po, porev, issued, category, ringi, vendor,
                                                 mouldcode, status, owner, oem, remarks, instockdate, instockdate50, pgroup, ac, cc, checkdate, checkdate2, cav, weight, equipment, shot, vertical, horizontal, height, ismodifiy, instockremarks,
                                                 collectdate, pass, pcs, rmb50, rmbtax, cndate, cnsenddate, chaseno);

                    DataService.GetInstance().ExecuteNonQuery(query);
                }

                MessageBox.Show("Record has been saved.");
            }
        }
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                List <int> invoiceIdList = new List <int>();

                foreach (DataRow row in table.Rows)
                {
                    string invoice    = row.ItemArray[0].ToString().Trim();
                    string vendor     = row.ItemArray[1].ToString().Trim();
                    string acccode    = row.ItemArray[3].ToString().Trim();
                    string costcentre = row.ItemArray[4].ToString().Trim();
                    string amount     = row.ItemArray[10].ToString().Trim();
                    string approval   = row.ItemArray[11].ToString().Trim();

                    if (approval == "Yes")
                    {
                        int    id     = GetInvoiceId(invoice);
                        string status = GetInvoiceStatus(invoice);

                        string query = status == "會計處理中" ? string.Format("update TB_ACC_OUTSTANDING_DETAIL set od_staffapproval = 'Yes' where od_o_id = '{0}' and od_accountcode = '{1}'" +
                                                                         " and od_costcentre = '{2}' and od_amount = '{3}'", id, acccode, costcentre, amount) : string.Format("update TB_ACC_OUTSTANDING_DETAIL set od_divapproval = 'Yes'" +
                                                                                                                                                                              " where od_o_id = '{0}' and od_accountcode = '{1}' and od_costcentre = '{2}' and od_amount = '{3}'", id, acccode, costcentre, amount);
                        DataServiceCM.GetInstance().ExecuteNonQuery(query);

                        invoiceIdList.Add(id);
                    }
                }

                invoiceIdList = invoiceIdList.Distinct().ToList();

                string now = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

                foreach (int item in invoiceIdList)
                {
                    string status  = GetInvoiceStatusById(item);
                    string invoice = GetInvoiceById(item);

                    string applicant = AccUtil.GetApplicant(invoice);
                    string div       = AccUtil.GetDivisionApprover(invoice);
                    string staff     = AccUtil.GetAccStaff(invoice);
                    string acc       = AccUtil.GetAccApprover(invoice);

                    if (status == "會計處理中")
                    {
                        if (IsAllItemApprovedByStaff(item))
                        {
                            string query = string.Format("update TB_ACC_OUTSTANDING set o_status = N'會計承認中', o_staffapproval = 'Yes', o_staffapprovaldate = '{0}' where o_id = '{1}'", now, item);
                            DataServiceCM.GetInstance().ExecuteNonQuery(query);

                            EformUtil.SendApprovalEmail(_invoice, applicant, AdUtil.GetEmailByUsername(applicant, "kmhk.local"), AdUtil.GetEmailByUsername(acc, "kmhk.local"), "", "Outstanding Slip - " + invoice);
                        }
                    }

                    if (status == "會計承認中")
                    {
                        if (IsAllItemApprovedByAcc(item))
                        {
                            string query = string.Format("update TB_ACC_OUTSTANDING set o_status = N'申請處理完成', o_accapproval = 'Yes', o_accapprovaldate = '{0}' where o_id = '{1}'", now, item);
                            DataServiceCM.GetInstance().ExecuteNonQuery(query);

                            EformUtil.SendFinishedEmail(_invoice, acc, AdUtil.GetEmailByUsername(acc, "kmhk.local"), AdUtil.GetEmailByUsername(applicant, "kmhk.local"), "Outstanding Slip Application Finished - " + invoice, "You Outstanding Slip Application has been finished.");
                        }
                    }
                }

                MessageBox.Show("Record has been uploaded.");
            }
        }
        private void btnInsert_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string chaseno = row.ItemArray[0].ToString().Trim();
                    string partno  = row.ItemArray[1].ToString().Trim();
                    string rev     = row.ItemArray[2].ToString().Trim();
                    if (rev.Length == 1)
                    {
                        rev = "0" + rev;
                    }
                    string mouldno = row.ItemArray[3].ToString().Trim();
                    string div     = row.ItemArray[4].ToString().Trim();
                    string curr    = row.ItemArray[5].ToString().Trim();
                    string amt     = row.ItemArray[6].ToString().Trim();
                    string amthkd  = row.ItemArray[7].ToString().Trim();
                    string mpa     = row.ItemArray[8].ToString().Trim();
                    string fa      = row.ItemArray[9].ToString().Trim();
                    string tmpfa   = row.ItemArray[10].ToString().Trim();
                    string qty     = row.ItemArray[11].ToString().Trim();
                    string model   = row.ItemArray[12].ToString().Trim();
                    string po      = row.ItemArray[13].ToString().Trim();
                    string porev   = row.ItemArray[14].ToString().Trim();
                    string issued  = ImportExcel2003.ParseDateTime(row.ItemArray[15].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (issued == "0001/01/01")
                    {
                        issued = row.ItemArray[15].ToString().Trim();
                    }
                    string category = row.ItemArray[16].ToString().Trim();
                    string ringi    = row.ItemArray[17].ToString().Trim();
                    string vendor   = row.ItemArray[18].ToString().Trim();
                    if (vendor.Length == 9)
                    {
                        vendor = "0" + vendor;
                    }
                    string mouldcode = row.ItemArray[19].ToString().Trim();
                    string status    = row.ItemArray[20].ToString().Trim();
                    string oem       = row.ItemArray[21].ToString().Trim();
                    string remarks   = row.ItemArray[22].ToString().Trim();
                    string instock   = ImportExcel2003.ParseDateTime(row.ItemArray[23].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (instock == "0001/01/01")
                    {
                        instock = row.ItemArray[23].ToString().Trim();
                    }
                    string instock50 = ImportExcel2003.ParseDateTime(row.ItemArray[24].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (instock50 == "0001/01/01")
                    {
                        instock50 = row.ItemArray[24].ToString().Trim();
                    }
                    string pgroup    = row.ItemArray[25].ToString().Trim();
                    string ac        = row.ItemArray[26].ToString().Trim();
                    string cc        = row.ItemArray[27].ToString().Trim();
                    string checkdate = ImportExcel2003.ParseDateTime(row.ItemArray[28].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (checkdate == "0001/01/01")
                    {
                        checkdate = row.ItemArray[28].ToString().Trim();
                    }
                    string cav         = row.ItemArray[29].ToString().Trim();
                    string weight      = row.ItemArray[30].ToString().Trim();
                    string equipment   = row.ItemArray[31].ToString().Trim();
                    string shot        = row.ItemArray[32].ToString().Trim();
                    string vertical    = row.ItemArray[33].ToString().Trim();
                    string horizontal  = row.ItemArray[34].ToString().Trim();
                    string height      = row.ItemArray[35].ToString().Trim();
                    string collectdate = ImportExcel2003.ParseDateTime(row.ItemArray[36].ToString().Trim()).ToString("yyyy/MM/dd");
                    if (collectdate == "0001/01/01")
                    {
                        collectdate = row.ItemArray[36].ToString().Trim();
                    }

                    string itemtext    = mouldno + "MP+" + mouldcode + "+" + div;
                    string projecttext = partno + "-" + rev;
                    string type        = "Single";
                    string owner       = "KDTHK";
                    string indate      = DateTime.Today.ToString("yyyy/MM/dd");
                    string request     = partno + rev;

                    if (chaseno == "")
                    {
                        continue;
                    }

                    string st    = status == "入庫濟" ? "S" : "K";
                    string query = string.Format("if not exists (select * from tb_betamould where tm_chaseno = '{0}') insert into tb_betamould (tm_chaseno, tm_itemcode, tm_rev, tm_mouldno" +
                                                 ", tm_status, tm_type, tm_currency, tm_amount, tm_amounthkd, tm_mpa, tm_fixedassetcode, tm_tmpfixedassetcode, tm_qty, tm_itemtext, tm_request, tm_indate, tm_projecttext" +
                                                 ", tm_model, tm_po, tm_porev, tm_poissued, tm_category, tm_ringi_code, tm_vendor_code, tm_mouldcode_code, tm_st_code, tm_owner, tm_rm, tm_instockdate" +
                                                 ", tm_instockdate50, tm_group, tm_accountcode, tm_costcentre, tm_checkdate, tm_cav, tm_weight, tm_accessory, tm_camera, tm_vertical, tm_horizontal, tm_height, tm_collectdate)" +
                                                 " values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}', '{24}', '{25}', '{26}', N'{27}'" +
                                                 ", '{28}', '{29}', '{30}', '{31}', '{32}', '{33}', '{34}', '{35}', '{36}', '{37}', '{38}', '{39}', '{40}', '{41}')", chaseno, partno, rev, mouldno, div, type, curr, amt, amthkd, mpa, fa, tmpfa,
                                                 qty, itemtext, request, indate, projecttext, model, po, porev, issued, category, ringi, vendor, mouldcode, st, owner, remarks, instock, instock50, pgroup, ac, cc, checkdate,
                                                 cav, weight, equipment, shot, vertical, horizontal, height, collectdate);

                    DataService.GetInstance().ExecuteNonQuery(query);
                }

                MessageBox.Show("Record has been saved.");
            }
        }
Esempio n. 18
0
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                List <Outstanding> list = new List <Outstanding>();

                bool isValid = true;

                foreach (DataRow row in table.Rows)
                {
                    string accountcode = row.ItemArray[0].ToString().Trim();
                    string costcentre  = row.ItemArray[1].ToString().Trim();
                    string amount      = row.ItemArray[2].ToString().Trim();
                    string desc1       = row.ItemArray[3].ToString().Trim();
                    string desc2       = row.ItemArray[4].ToString().Trim();
                    string desc3       = row.ItemArray[5].ToString().Trim();
                    string desc4       = row.ItemArray[6].ToString().Trim();
                    string desc5       = row.ItemArray[7].ToString().Trim();

                    if (!AccUtil.IsAccountCodeExists(accountcode))
                    {
                        isValid = false;
                    }

                    if (!AccUtil.IsCostCentreExists(costcentre))
                    {
                        isValid = false;
                    }

                    if (!IsAmountValid(amount))
                    {
                        isValid = false;
                    }

                    if (desc1.Length > 50 || desc2.Length > 50 || desc3.Length > 50 || desc4.Length > 50 || desc5.Length > 50)
                    {
                        isValid = false;
                    }

                    //list.Add(new Outstanding { AccountCode = accountcode, AccountName = "", CostCentre = costcentre, CostCentreName = "", Amount = amount, Desc1 = desc1, Desc2 = desc2, Desc3 = desc3, Desc4 = desc4, Desc5 = desc5 });
                }

                if (isValid)
                {
                    foreach (Outstanding item in list)
                    {
                        dgvOutstanding.Rows.Add(item.AccountCode, AccUtil.GetAccountName(item.AccountCode), item.CostCentre, AccUtil.GetCostCentreName(item.CostCentre), item.Amount, item.Desc1, item.Desc2, item.Desc3, item.Desc4, item.Desc5);
                    }

                    GetTotalAmount();
                }
                else
                {
                    MessageBox.Show("Invalid AccountCode / CostCentre / Amount input. Please revise data.");
                }
            }
        }
        private void tsbtnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
                         "All Excel Types|*.xls;*.xlsx";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string mouldCode = row.ItemArray[0].ToString();
                    string type      = row.ItemArray[1].ToString();
                    string jp        = row.ItemArray[2].ToString();
                    string eng       = row.ItemArray[3].ToString();
                    string chin      = row.ItemArray[4].ToString();
                    string itemGroup = row.ItemArray[5].ToString();

                    string query = string.Format("if not exists (select * from TB_MASTER_MOULDCODE where mc_code = '{0}')" +
                                                 " insert into TB_MASTER_MOULDCODE (mc_code, mc_type, mc_contentjp, mc_contenteng, mc_contentchin, mc_itemgroup)" +
                                                 " values ('{0}', N'{1}', N'{2}', N'{3}', N'{4}', '{5}') else update TB_MASTER_MOULDCODE set mc_type = N'{1}'" +
                                                 ", mc_contentjp = N'{2}', mc_contenteng = N'{3}', mc_contentchin = N'{4}', mc_itemgroup = '{5}' where mc_code = '{0}'",
                                                 mouldCode, type, jp, eng, chin, itemGroup);
                    DataService.GetInstance().ExecuteNonQuery(query);
                }
                this.LoadData(txtSearch.Text);
            }
        }
Esempio n. 20
0
        private void tsbtnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog()
            {
                Filter = "Excel Files |*.xls"
            };

            try
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);

                    foreach (DataRow row in table.Rows)
                    {
                        string refno      = row.ItemArray[1].ToString();
                        string type       = row.ItemArray[3].ToString();
                        string question   = row.ItemArray[4].ToString();
                        string ansA       = row.ItemArray[5].ToString();
                        string ansB       = row.ItemArray[6].ToString();
                        string ansC       = row.ItemArray[7].ToString();
                        string ansD       = row.ItemArray[8].ToString();
                        string correct    = row.ItemArray[9].ToString();
                        string questionjp = row.ItemArray[10].ToString();
                        string ansAjp     = row.ItemArray[11].ToString();
                        string ansBjp     = row.ItemArray[12].ToString();
                        string ansCjp     = row.ItemArray[13].ToString();
                        string ansDjp     = row.ItemArray[14].ToString();
                        string path       = row.ItemArray[15].ToString();
                        string pathjpy    = row.ItemArray[16].ToString();
                        string aspath     = row.ItemArray[17].ToString();
                        string aspathjpy  = row.ItemArray[18].ToString();

                        int typeid = EducationUtil.GetTypeId(type);

                        int count = 0;

                        string questiontext = string.Format("insert into TB_QUESTION (q_content, q_type" +
                                                            ", q_typeid, q_contentjp, q_mgtno, q_path, q_pathjpy, q_aspath, q_aspathjpy)" +
                                                            " values (N'{0}', '{1}', '{2}', N'{3}', '{4}', N'{5}', N'{6}', N'{7}', N'{8}')", question,
                                                            type, typeid, questionjp, refno, path, pathjpy, aspath, aspathjpy);

                        DataService.GetInstance().ExecuteNonQuery(questiontext);

                        int questionid = EducationUtil.GetQuestionId(question);

                        string[] array = { "A.", "B.", "C.", "D." };

                        for (int i = 0; i < 4; i++)
                        {
                            string symbol     = array[i];
                            string ans        = symbol == "A." ? ansA : symbol == "B." ? ansB : symbol == "C." ? ansC : ansD;
                            string ansjp      = symbol == "A." ? ansAjp : symbol == "B." ? ansBjp : symbol == "C." ? ansCjp : ansDjp;
                            string cor        = correct == symbol ? "True" : "False";
                            string answertext = string.Format("insert into TB_ANSWER (a_questionid, a_symbol, a_answer" +
                                                              ", a_answerjp, a_correct) values ('{0}', '{1}', N'{2}', N'{3}', '{4}')", questionid, symbol, ans, ansjp, cor);

                            DataService.GetInstance().ExecuteNonQuery(answertext);
                        }
                    }
                    LoadData();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message + ex.StackTrace);
            }
        }
Esempio n. 21
0
        private void tsbtnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
                         "All Excel Types|*.xls;*.xlsx";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string vendor   = row.ItemArray[0].ToString();
                    string name     = row.ItemArray[1].ToString();
                    string pGroup   = row.ItemArray[2].ToString();
                    string currency = row.ItemArray[3].ToString();
                    string payTerms = row.ItemArray[4].ToString();
                    string region   = row.ItemArray[5].ToString();
                    string request  = row.ItemArray[6].ToString();
                    string edi      = row.ItemArray[7].ToString();
                    string remarks  = row.ItemArray[8].ToString();

                    string query = string.Format("if not exists (select * from TB_MASTER_VENDOR where mv_code = '{0}')" +
                                                 " insert into TB_MASTER_VENDOR (mv_code, mv_name, mv_group, mv_detail, mv_paycurr" +
                                                 ", mv_payterms, mv_request, mv_edi, mv_remarks) values ('{0}', N'{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', N'{8}')" +
                                                 " else update TB_MASTER_VENDOR set mv_name = N'{1}', mv_group = '{2}', mv_detail = '{3}'" +
                                                 ", mv_paycurr = '{4}', mv_payterms = '{5}', mv_request = '{6}', mv_edi = '{7}', mv_remarks = N'{8}'" +
                                                 " where mv_code = '{0}'", vendor, name, pGroup, region, currency, payTerms, request, edi, remarks);
                    DataService.GetInstance().ExecuteNonQuery(query);
                }
                this.LoadData(txtSearch.Text);
            }
        }
        private void UploadData2()
        {
            OpenFileDialog ofd = new OpenFileDialog();

            DataTable tbUpload = new DataTable();

            string[] simHeaders = { "code", "name", "invoice", "total", "detail" };
            foreach (string header in simHeaders)
            {
                tbUpload.Columns.Add(header);
            }

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith(".xlsx") ? ImportExcel2007.TranslateToTable(ofd.FileName, "Template") : ImportExcel2003.TranslateToTable(ofd.FileName, "Template");

                bool isValid = true;

                int index = 1;

                List <OutstandingError> errorList = new List <OutstandingError>();

                List <Outstanding> list = new List <Outstanding>();

                foreach (DataRow row in table.Rows)
                {
                    string code = row.ItemArray[0].ToString().Trim();
                    if (code.Length == 9 && code.StartsWith("23"))
                    {
                        code = "0" + code;
                    }
                    string invoice    = row.ItemArray[1].ToString().Trim();
                    string acccode    = row.ItemArray[2].ToString().Trim();
                    string costcentre = row.ItemArray[3].ToString().Trim();
                    string currency   = row.ItemArray[4].ToString().Trim().ToUpper();
                    string amount     = row.ItemArray[5].ToString().Trim();
                    string desc1      = row.ItemArray[6].ToString().Trim();
                    string desc2      = row.ItemArray[7].ToString().Trim();
                    string desc3      = row.ItemArray[8].ToString().Trim();
                    string desc4      = row.ItemArray[9].ToString().Trim();
                    string desc5      = row.ItemArray[10].ToString().Trim();

                    string vcurr = AccUtil.GetVendorCurrency(code);

                    double amt;
                    //double tmpAmt = Convert.ToDouble(amount.Replace("-", ""));
                    bool isNumeric = double.TryParse(amount, out amt);

                    if (AccUtil.IsInvoiceExists(invoice, code))
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Invoice: " + invoice + " has been used"
                        });
                        isValid = false;
                    }

                    if (!AccUtil.IsVendorExists(code))
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Vendor code: " + code + " does not exist"
                        });
                        isValid = false;
                    }

                    if (!AccUtil.IsAccountCodeExists(acccode))
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Account Code: " + acccode + " does not exist"
                        });
                        isValid = false;
                    }

                    if (!AccUtil.IsCostCentreExists(costcentre))
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "CostCentre: " + costcentre + " does not exist"
                        });
                        isValid = false;
                    }

                    if (currency != "HKD" && currency != "USD" && currency != "JPY" && currency != "EUR" && currency != "RMB")
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Invalid currency format"
                        });
                        isValid = false;
                    }

                    if (currency != vcurr)
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Mismatched currency. The correct currency is " + vcurr
                        });
                        isValid = false;
                    }

                    if (!isNumeric)
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Amount: " + amount + " is not valid"
                        });
                        isValid = false;
                    }

                    amount = string.Format("{0:0.00}", Convert.ToDouble(amount));

                    var regex = new Regex(@"^\d+\.\d{2}?$");
                    if (!regex.IsMatch(amount) && !amount.StartsWith("-"))
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Amount: " + amount + " is not valid"
                        });
                        isValid = false;
                    }

                    if (desc1.Length == 0)
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Remarks 1 must be input"
                        });
                        isValid = false;
                    }

                    if (desc1.Length > 50 || desc2.Length > 50 || desc3.Length > 50 || desc4.Length > 50 || desc5.Length > 50)
                    {
                        errorList.Add(new OutstandingError {
                            index = index, Message = "Each remarks field length cannot over 50 characters"
                        });
                        isValid = false;
                    }

                    if (isValid)
                    {
                        list.Add(new Outstanding {
                            VendorCode = code, Invoice = invoice, AccountCode = acccode, CostCentre = costcentre, Amount = amount, Desc1 = desc1, Desc2 = desc2, Desc3 = desc3, Desc4 = desc4, Desc5 = desc5
                        });
                    }

                    index++;
                }

                if (errorList.Count > 0)
                {
                    OutstandingErrorBox form = new OutstandingErrorBox(errorList);
                    form.ShowDialog();
                }
                else
                {
                    var groupedList = from x in list
                                      group x by new
                    {
                        x.Invoice,
                        x.VendorCode,
                    } into g
                        select new
                    {
                        Invoice    = g.Key.Invoice,
                        VendorCode = g.Key.VendorCode,
                        Value      = g.Sum(y => Convert.ToDecimal(y.Amount))
                    };

                    foreach (var item in groupedList)
                    {
                        string query = string.Format("select v_name from TB_ACC_MASTER_VENDOR where v_code = '{0}'", item.VendorCode);
                        string name  = DataServiceCM.GetInstance().ExecuteScalar(query).ToString().Trim();

                        //Debug.WriteLine(item.Value.ToString());
                        tbUpload.Rows.Add(item.VendorCode, name, item.Invoice, item.Value.ToString(), "Detail");
                    }

                    OutstandingPreviewForm form = new OutstandingPreviewForm(tbUpload, list);
                    form.ShowDialog();
                }
            }
        }
Esempio n. 23
0
        private void tsbtnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
                         "All Excel Types|*.xls;*.xlsx";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string chaseNo   = row.ItemArray[0].ToString();
                    string requestNo = row.ItemArray[6].ToString();
                    string po        = row.ItemArray[7].ToString();

                    if (po != "")
                    {
                        string today = DateTime.Today.ToString("yyyy/MM/dd");
                        string query = string.Format("update TB_MOULD_MAIN set mm_requestno = '{0}', mm_po = '{1}', mm_poissued = '{2}', mm_status_code = 'P' where mm_chaseno = '{3}'", requestNo, po, today, chaseNo);
                        DataService.GetInstance().ExecuteNonQuery(query);
                    }
                }
                MessageBox.Show("Record has been saved.");
                this.LoadData(txtSearch.Text);
            }
        }
Esempio n. 24
0
        private void tsbtnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
                         "All Excel Types|*.xls;*.xlsx";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                }
            }
        }
        private void tsbtnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
                         "All Excel Types|*.xls;*.xlsx";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);

                foreach (DataRow row in table.Rows)
                {
                    string code        = row.ItemArray[0].ToString();
                    string content     = row.ItemArray[1].ToString();
                    string accountCode = row.ItemArray[2].ToString();
                    string costCentre  = row.ItemArray[3].ToString();
                    string remarks     = row.ItemArray[4].ToString();

                    string query = string.Format("if not exists (select * from TB_MASTER_OEM where mo_code = '{0}')" +
                                                 " insert into TB_MASTER_OEM (mo_code, mo_content, mo_accountcode, mo_costcentre, mo_remarks)" +
                                                 " values ('{0}', N'{1}', '{2}', '{3}', N'{4}') else update TB_MASTER_OEM set mo_content = N'{1}', mo_accountcode = '{2}'" +
                                                 ", mo_costcentre = '{3}', mo_remarks = N'{4}' where mo_code = '{0}'", code, content, accountCode, costCentre, remarks);

                    DataService.GetInstance().ExecuteNonQuery(query);
                }
            }

            this.LoadData(txtSearch.Text);
        }
Esempio n. 26
0
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);

                string text = "delete from tb_mould_cost dbcc checkident ('tb_mould_cost', reseed, 0)";
                DataService.GetInstance().ExecuteNonQuery(text);

                foreach (DataRow row in table.Rows)
                {
                    string itemcode = row.ItemArray[0].ToString().Trim();
                    string rev      = row.ItemArray[1].ToString().Trim();
                    if (rev.Length == 1)
                    {
                        rev = "0" + rev;
                    }
                    string currency = row.ItemArray[2].ToString().Trim();
                    string cost     = Math.Round(Convert.ToDecimal(row.ItemArray[3]), 0).ToString();

                    string remarks = row.ItemArray[4].ToString().Trim();

                    string query = string.Format("insert into tb_mould_cost (c_itemcode, c_rev, c_currency, c_cost, c_remarks) values ('{0}', '{1}', '{2}', '{3}', N'{4}')", itemcode, rev, currency, cost, remarks);
                    DataService.GetInstance().ExecuteNonQuery(query);
                }

                MessageBox.Show("Record has been saved.");
            }

            LoadData("");
        }