Пример #1
0
        public bool InsertRowofWorkingDate(WorkingDateData workData)
        {
            try
            {
                string sqlQuerry = "";
                sqlQuerry += "insert into m_WorkingDate ( IDName,Name, Dept,Col_Dept,Col_date,Year,Month,Shift, N1, N2, N3, N4, N5, N6, N7, N8, N9, N10,N11,"
                             + "N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23,N24,N25,N26,N27,N28,N29,N30,N31, Update_date ) values ('";
                sqlQuerry += workData.ID + "', '" + workData.Name + "', '" + workData.dept + "', '" + workData.col_Dept + "', '" + workData.col_date + "', '" + "2019" + "', '"
                             + workData.WorkingTimeDatas[0].month + "', '" + "day";

                for (int i = 0; i < workData.WorkingTimeDatas.Count; i++)
                {
                    if (workData.WorkingTimeDatas[i].Shift == "day")
                    {
                        sqlQuerry += "', '" + workData.WorkingTimeDatas[i].workingHour;
                    }
                }
                sqlQuerry += "',GETDATE() )";
                sqlCON sqlCON = new sqlCON();
                sqlCON.sqlExecuteNonQuery(sqlQuerry, false);


                string sqlQuerry2 = "";
                sqlQuerry2 += "insert into m_WorkingDate ( IDName,Name, Dept,Col_Dept,Col_date,Year,Month,Shift, N1, N2, N3, N4, N5, N6, N7, N8, N9, N10,N11,"
                              + "N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23,N24,N25,N26,N27,N28,N29,N30,N31, Update_date ) values ('";
                sqlQuerry2 += workData.ID + "', '" + workData.Name + "', '" + workData.dept + "', '" + workData.col_Dept + "', '" + workData.col_date + "', '" + "2019" + "', '"
                              + workData.WorkingTimeDatas[0].month + "', '" + "night";

                for (int i = 0; i < workData.WorkingTimeDatas.Count; i++)
                {
                    if (workData.WorkingTimeDatas[i].Shift == "night")
                    {
                        sqlQuerry2 += "', '" + workData.WorkingTimeDatas[i].workingHour;
                    }
                }
                sqlQuerry2 += "',GETDATE() )";
                sqlCON sqlCON2 = new sqlCON();
                return(sqlCON2.sqlExecuteNonQuery(sqlQuerry2, false));
            }
            catch (Exception ex)
            {
                return(false);
            }
        }
        public List <WorkingDateData> GetWorkingDateDatasVanPhong(string pathExcelFile, int month)
        {
            List <WorkingDateData> getworkingDateDatas = new List <WorkingDateData>();

            try
            {
                //Create COM Objects. Create a COM object for everything that is referenced
                Excel.Application xlApp      = new Excel.Application();
                Excel.Workbook    xlWorkbook = xlApp.Workbooks.Open(pathExcelFile);
                for (int k = 1; k < 37; k++)
                {
                    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[k];
                    Excel.Range      xlRange     = xlWorksheet.UsedRange;

                    int rowCount = xlRange.Rows.Count;
                    int colCount = xlRange.Columns.Count;

                    //iterate over the rows and columns and print to the console as it appears in the file
                    //excel is not zero based!!
                    for (int i = 6; i <= rowCount; i = i + 1)
                    {
                        WorkingDateData data = new WorkingDateData();
                        data.dept             = (xlRange.Cells[i, 2].Value != null) ? xlRange.Cells[i, 2].Value.ToString() : "";
                        data.ID               = (xlRange.Cells[i, 4].Value != null) ? xlRange.Cells[i, 4].Value.ToString() : "";
                        data.Name             = (xlRange.Cells[i, 5].Value != null) ? xlRange.Cells[i, 5].Value.ToString() : "";
                        data.col_Dept         = (xlRange.Cells[i, 6].Value != null) ? xlRange.Cells[i, 6].Value.ToString() : "";
                        data.col_date         = (xlRange.Cells[i, 7].Value != null) ? xlRange.Cells[i, 7].Value.ToString() : "";
                        data.WorkingTimeDatas = new List <WorkingTimeData>();
                        for (int j = 1; j < 32; j++)
                        {
                            data.WorkingTimeDatas.Add(new WorkingTimeData {
                                month = month.ToString(), ngay = j.ToString(), Shift = "day", workingHour = (xlRange.Cells[i, 9 + j].Value != null) ? xlRange.Cells[i, 9 + j].Value.ToString() : ""
                            });
                            //    data.WorkingTimeDatas.Add(new WorkingTimeData { month = month.ToString(), ngay = j.ToString(), Shift = "night", workingHour = (xlRange.Cells[i + 1, 9 + j].Value != null) ? xlRange.Cells[i + 1, 9 + j].Value.ToString() : "" });
                        }
                        if (data.ID != "")
                        {
                            DataControl dataControl = new DataControl();
                            dataControl.InsertRowofWorkingDate(data);
                            getworkingDateDatas.Add(data);
                        }
                    }
                    //cleanup
                    GC.Collect();
                    GC.WaitForPendingFinalizers();

                    //rule of thumb for releasing com objects:
                    //  never use two dots, all COM objects must be referenced and released individually
                    //  ex: [somthing].[something].[something] is bad

                    //release com objects to fully kill excel process from running in the background
                    Marshal.ReleaseComObject(xlRange);
                    Marshal.ReleaseComObject(xlWorksheet);
                }



                //close and release
                xlWorkbook.Close();
                Marshal.ReleaseComObject(xlWorkbook);

                //quit and release
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
            }
            catch (Exception ex)
            {
                Logfile.Output(StatusLog.Error, "List<WorkingDateData> GetWorkingDateDatas(string pathExcelFile, int month)", ex.Message);
            }
            return(getworkingDateDatas);
        }