public ActionResult Create(TRN_CHALLAN_UPLOAD upload)
        {
            decimal?USER_NO  = Session["sess_USER_NO"] as decimal?;
            decimal?LOGON_NO = Session["sess_LOGON_NO"] as decimal?;

            ViewBag.UPLOAD_TYPE_NUM = upload_types;

            if (upload.uploadExcel.ContentLength > 0)
            {
                int length = upload.uploadExcel.ContentLength;

                upload.FILE_NAME = upload.uploadExcel.FileName;
                upload.FILE_TYPE = upload.uploadExcel.ContentType;
                upload.FILE_EXT  = Path.GetExtension(upload.uploadExcel.FileName);

                if (!System.IO.Directory.Exists(challan_upload_url))
                {
                    System.IO.Directory.CreateDirectory(challan_upload_url);
                }

                string fileLocation = challan_upload_url + Path.GetFileNameWithoutExtension(upload.FILE_NAME)
                                      + DateTime.Now.ToString("_yyyy_MM_dd_hh_mm_ss_") + DateTime.Now.Millisecond
                                      + Path.GetExtension(upload.FILE_NAME);
                while (System.IO.File.Exists(fileLocation))
                {
                    fileLocation = challan_upload_url + Path.GetFileNameWithoutExtension(upload.FILE_NAME)
                                   + DateTime.Now.ToString("_yyyy_MM_dd_hh_mm_ss_") + DateTime.Now.Millisecond
                                   + Path.GetExtension(upload.FILE_NAME);
                }
                upload.uploadExcel.SaveAs(fileLocation);
                upload.FILE_LOCATION = fileLocation;


                Microsoft.Office.Interop.Excel.Application xlApp       = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(fileLocation);
                Microsoft.Office.Interop.Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
                Microsoft.Office.Interop.Excel.Range       xlRange     = xlWorksheet.UsedRange;

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

                bool fields_valid = true;

                if (upload.UPLOAD_TYPE_NUM == (decimal)EChallanTypes.Challan)
                {
                    List <TRN_CHALLAN_DET> challans = new List <TRN_CHALLAN_DET>();

                    List <string> HrId_List        = new List <string>();
                    List <string> Book_List        = new List <string>();
                    string        HrId_List_String = null;
                    string        Book_List_String = null;

                    string challan_no   = xlRange.Cells[1, 1].Value2.ToString();
                    string challan_date = xlRange.Cells[1, 2].Value2.ToString();

                    if (challan_no != "Chalan_No")
                    {
                        ModelState.AddModelError("ERROR_MSG", "Chalan_No doesn't exist for Challan Type Upload");
                        fields_valid = false;
                    }
                    if (challan_date != "Chalan_Date")
                    {
                        ModelState.AddModelError("ERROR_MSG", "Chalan_Date doesn't exist for Challan Type Upload");
                        fields_valid = false;
                    }

                    if (fields_valid)
                    {
                        for (int i = 2; i <= rowCount; i++)
                        {
                            TRN_CHALLAN_DET newChallan = new TRN_CHALLAN_DET();
                            newChallan.CHALLAN_NO      = xlRange.Cells[i, 1].Value2.ToString();
                            newChallan.CHALLAN_DATE    = DateTime.FromOADate(Double.Parse(xlRange.Cells[i, 2].Value2.ToString()));
                            newChallan.BB_LIBRARY_CODE = xlRange.Cells[i, 3].Value2.ToString();
                            newChallan.EMP_HR_ID       = xlRange.Cells[i, 4].Value2.ToString();
                            newChallan.BB_BOOK_CODE    = xlRange.Cells[i, 5].Value2.ToString();
                            newChallan.BB_UNIQUE_CODE  = xlRange.Cells[i, 6].Value2.ToString();
                            newChallan.SUPPLY_QTY      = decimal.Parse(xlRange.Cells[i, 7].Value2.ToString());
                            newChallan.DIST_MAX_DAY    = decimal.Parse(xlRange.Cells[i, 8].Value2.ToString());

                            HrId_List.Add(newChallan.EMP_HR_ID);
                            Book_List.Add(newChallan.BB_UNIQUE_CODE);

                            challans.Add(newChallan);
                        }

                        HrId_List = HrId_List.Distinct <string>().ToList();
                        Book_List = Book_List.Distinct <string>().ToList();

                        HrId_List_String = string.Join(",", HrId_List.ToArray());
                        Book_List_String = string.Join(",", Book_List.ToArray());

                        CHALLAN_VALIDITY_CHECK_Result non_existent =
                            db.CHALLAN_VALIDITY_CHECK(HrId_List_String, Book_List_String).ToList().FirstOrDefault();
                        if (non_existent.V_HR_COUNT > 0)
                        {
                            ModelState.AddModelError("ERROR_MSG", "Employee HR Id is not valid " + non_existent.V_HR_LIST);
                        }
                        if (non_existent.V_BOOK_COUNT > 0)
                        {
                            ModelState.AddModelError("ERROR_MSG", "Book Unique Code is not valid " + non_existent.V_BOOK_LIST);
                        }

                        if (non_existent.V_HR_LIST == null)
                        {
                            non_existent.V_HR_LIST = string.Empty;
                        }
                        if (non_existent.V_BOOK_LIST == null)
                        {
                            non_existent.V_BOOK_LIST = string.Empty;
                        }

                        if (ModelState.IsValid)
                        {
                            decimal?UPLOAD_NO = db.TRN_CHALLAN_UPLOAD_INSERT(USER_NO, LOGON_NO, upload.FILE_NAME, upload.FILE_LOCATION,
                                                                             upload.FILE_TYPE, upload.FILE_EXT, (decimal)(rowCount - 1), (decimal)HrId_List.Count,
                                                                             (decimal)Book_List.Count, (decimal)non_existent.V_HR_COUNT, (decimal)non_existent.V_BOOK_COUNT,
                                                                             non_existent.V_HR_LIST, non_existent.V_BOOK_LIST, upload.UPLOAD_TYPE_NUM).FirstOrDefault().UPLOAD_NO;
                            if (UPLOAD_NO.HasValue)
                            {
                                foreach (TRN_CHALLAN_DET challan in challans)
                                {
                                    try
                                    {
                                        db.TRN_CHALLAN_DET_INSERT(USER_NO, LOGON_NO, UPLOAD_NO, challan.CHALLAN_NO,
                                                                  challan.CHALLAN_DATE, challan.BB_LIBRARY_CODE, challan.EMP_HR_ID, challan.BB_BOOK_CODE,
                                                                  challan.BB_UNIQUE_CODE, challan.SUPPLY_QTY, challan.DIST_MAX_DAY);
                                    }
                                    catch (Exception e)
                                    {
                                        ModelState.AddModelError("ERROR_MSG", e.InnerException.Message);
                                        return(View(upload));
                                    }
                                }
                            }

                            return(RedirectToAction("Index"));
                        }
                    }
                }
                else if (upload.UPLOAD_TYPE_NUM == (decimal)EChallanTypes.Return)
                {
                    List <TRN_RETURN_DET> returns = new List <TRN_RETURN_DET>();

                    List <string> HrId_List        = new List <string>();
                    List <string> Book_List        = new List <string>();
                    string        HrId_List_String = null;
                    string        Book_List_String = null;

                    string return_no   = xlRange.Cells[1, 1].Value2.ToString();
                    string return_date = xlRange.Cells[1, 2].Value2.ToString();

                    if (return_no != "Return_Slip_No")
                    {
                        ModelState.AddModelError("ERROR_MSG", "Return_Slip_No doesn't exist for Return Type Upload");
                        fields_valid = false;
                    }
                    if (return_date != "Return_Date")
                    {
                        ModelState.AddModelError("ERROR_MSG", "Return_Date doesn't exist for Return Type Upload");
                        fields_valid = false;
                    }

                    if (fields_valid)
                    {
                        for (int i = 2; i <= rowCount; i++)
                        {
                            TRN_RETURN_DET newReturn = new TRN_RETURN_DET();
                            newReturn.RETURN_SLIP_NO  = xlRange.Cells[i, 1].Value2.ToString();;
                            newReturn.RETURN_DATE     = DateTime.FromOADate(Double.Parse(xlRange.Cells[i, 2].Value2.ToString()));
                            newReturn.BB_LIBRARY_CODE = xlRange.Cells[i, 3].Value2.ToString();
                            newReturn.EMP_HR_ID       = xlRange.Cells[i, 4].Value2.ToString();
                            newReturn.BB_BOOK_CODE    = xlRange.Cells[i, 5].Value2.ToString();
                            newReturn.BB_UNIQUE_CODE  = xlRange.Cells[i, 6].Value2.ToString();
                            newReturn.RETURN_QTY      = decimal.Parse(xlRange.Cells[i, 7].Value2.ToString());

                            HrId_List.Add(newReturn.EMP_HR_ID);
                            Book_List.Add(newReturn.BB_UNIQUE_CODE);

                            returns.Add(newReturn);
                        }

                        HrId_List = HrId_List.Distinct <string>().ToList();
                        Book_List = Book_List.Distinct <string>().ToList();

                        HrId_List_String = string.Join(",", HrId_List.ToArray());
                        Book_List_String = string.Join(",", Book_List.ToArray());

                        CHALLAN_VALIDITY_CHECK_Result non_existent =
                            db.CHALLAN_VALIDITY_CHECK(HrId_List_String, Book_List_String).ToList().FirstOrDefault();
                        if (non_existent.V_HR_COUNT > 0)
                        {
                            ModelState.AddModelError("ERROR_MSG", "Employee HR Id is not valid " + non_existent.V_HR_LIST);
                        }
                        if (non_existent.V_BOOK_COUNT > 0)
                        {
                            ModelState.AddModelError("ERROR_MSG", "Book Unique Code is not valid " + non_existent.V_BOOK_LIST);
                        }

                        if (non_existent.V_HR_LIST == null)
                        {
                            non_existent.V_HR_LIST = string.Empty;
                        }
                        if (non_existent.V_BOOK_LIST == null)
                        {
                            non_existent.V_BOOK_LIST = string.Empty;
                        }

                        if (ModelState.IsValid)
                        {
                            decimal?UPLOAD_NO = db.TRN_CHALLAN_UPLOAD_INSERT(USER_NO, LOGON_NO, upload.FILE_NAME, upload.FILE_LOCATION,
                                                                             upload.FILE_TYPE, upload.FILE_EXT, (decimal)(rowCount - 1), (decimal)HrId_List.Count,
                                                                             (decimal)Book_List.Count, (decimal)non_existent.V_HR_COUNT, (decimal)non_existent.V_BOOK_COUNT,
                                                                             non_existent.V_HR_LIST, non_existent.V_BOOK_LIST, upload.UPLOAD_TYPE_NUM).FirstOrDefault().UPLOAD_NO;

                            if (UPLOAD_NO.HasValue)
                            {
                                foreach (TRN_RETURN_DET ret in returns)
                                {
                                    try
                                    {
                                        db.TRN_RETURN_DET_INSERT(USER_NO, LOGON_NO, UPLOAD_NO, ret.RETURN_SLIP_NO,
                                                                 ret.RETURN_DATE, ret.BB_LIBRARY_CODE, ret.EMP_HR_ID, ret.BB_BOOK_CODE,
                                                                 ret.BB_UNIQUE_CODE, ret.RETURN_QTY);
                                    }
                                    catch (Exception e)
                                    {
                                        ModelState.AddModelError("ERROR_MSG", e.InnerException.Message);
                                        return(View(upload));
                                    }
                                }
                            }

                            return(RedirectToAction("Index"));
                        }
                    }
                }
            }

            return(View(upload));
        }