예제 #1
0
        public static ImportFee GetOrCreateImportFeeByID(int ID, bool CanCreate = true)
        {
            ImportFee data = null;

            using (SqlHelper helper = new SqlHelper())
            {
                try
                {
                    helper.BeginTransaction();
                    data = GetOrCreateImportFeeByID(ID, helper, CanCreate: CanCreate);
                    helper.Commit();
                }
                catch (Exception)
                {
                    helper.Rollback();
                }
            }
            return(data);
        }
예제 #2
0
        public static ImportFee GetOrCreateImportFeeByID(int ID, SqlHelper helper, bool CanCreate = true)
        {
            var data = ImportFee.GetImportFee(ID, helper);

            if (!CanCreate)
            {
                return(data);
            }
            if (data != null)
            {
                return(data);
            }
            #region  除备份还原
            List <SqlParameter> parameters = new List <SqlParameter>();
            parameters.Add(new SqlParameter("@ID", ID));
            var bak_data = GetOne <ImportFeeBak>("select * from [ImportFeeBak] where [ID] = @ID", parameters, helper);
            if (bak_data != null)
            {
                string cmdtext = string.Empty;
                cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
                cmdtext += @"
                    insert into [ImportFee]
                    ([ID]
                    ,[RoomID]
                    ,[ChargeDate]
                    ,[ChargeID]
                    ,[StartPoint]
                    ,[EndPoint]
                    ,[TotalPoint]
                    ,[UnitPrice]
                    ,[TotalPrice]
                    ,[WriteDate]
                    ,[StartTime]
                    ,[EndTime]
                    ,[AddTime]
                    ,[ChargeStatus]
                    ,[ImportCoefficient]
                    ,[ImportBiaoCategory]
                    ,[ImportBiaoName]
                    ,[ChargeBiaoID]
                    ,[ProjectBiaoID]
                    ,[ImportBiaoGuiGe]
                    ,[ImportRate]
                    ,[ImportReducePoint]
                    ,[ImportChargeRoomNo])
                    select 
                    [ID]
                    ,[RoomID]
                    ,[ChargeDate]
                    ,[ChargeID]
                    ,[StartPoint]
                    ,[EndPoint]
                    ,[TotalPoint]
                    ,[UnitPrice]
                    ,[TotalPrice]
                    ,[WriteDate]
                    ,[StartTime]
                    ,[EndTime]
                    ,getdate()
                    ,[ChargeStatus]
                    ,[ImportCoefficient]
                    ,[ImportBiaoCategory]
                    ,[ImportBiaoName]
                    ,[ChargeBiaoID]
                    ,[ProjectBiaoID]
                    ,[ImportBiaoGuiGe]
                    ,[ImportRate]
                    ,[ImportReducePoint]
                    ,[ImportChargeRoomNo]
                    from [ImportFeeBak] where ID=@ID;";
                cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
                int count = helper.Execute(cmdtext, CommandType.Text, parameters);
                if (count > 0)
                {
                    data = ImportFee.GetImportFee(ID, helper);
                }
            }
            #endregion
            #region 账单明细还原
            if (data == null)
            {
                var roomfee = RoomFee.GetRoomFeeByImportFeeID(ID, helper);
                if (roomfee != null)
                {
                    decimal EndPoint          = roomfee.UseCount > 0 ? roomfee.UseCount : 0;
                    decimal UnitPrice         = roomfee.UnitPrice > 0 ? roomfee.UnitPrice : 0;
                    decimal RealCost          = roomfee.RealCost > 0 ? roomfee.RealCost : 0;
                    string  WriteDate         = "'" + (roomfee.RoomFeeWriteDate > DateTime.MinValue ? roomfee.RoomFeeWriteDate.ToString("yyyy-MM-dd") : DateTime.Now.ToString("yyyy-MM-dd")) + "'";
                    string  StartTime         = roomfee.StartTime > DateTime.MinValue ? "'" + roomfee.StartTime.ToString("yyyy-MM-dd") + "'" : "NULL";
                    string  EndTime           = roomfee.EndTime > DateTime.MinValue ? "'" + roomfee.EndTime.ToString("yyyy-MM-dd") + "'" : "NULL";
                    decimal ImportCoefficient = roomfee.RoomFeeCoefficient > 0 ? roomfee.RoomFeeCoefficient : 0;
                    string  cmdtext           = string.Empty;
                    cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
                    cmdtext += @"insert into [ImportFee]
                    ([ID]
                    ,[RoomID]
                    ,[ChargeDate]
                    ,[ChargeID]
                    ,[StartPoint]
                    ,[EndPoint]
                    ,[TotalPoint]
                    ,[UnitPrice]
                    ,[TotalPrice]
                    ,[WriteDate]
                    ,[StartTime]
                    ,[EndTime]
                    ,[AddTime]
                    ,[ChargeStatus]
                    ,[ImportCoefficient]
                    ,[ImportBiaoCategory]
                    ,[ImportBiaoName]
                    ,[ChargeBiaoID]
                    ,[ProjectBiaoID]
                    ,[ImportBiaoGuiGe]
                    ,[ImportRate]
                    ,[ImportReducePoint]
                    ,[ImportChargeRoomNo])
                    values( 
                     " + ID + @"
                    ," + roomfee.RoomID + @"
                    ,NULL
                    ," + roomfee.ChargeID + @"
                    ,0
                    ," + EndPoint + @"
                    ," + EndPoint + @"
                    ," + UnitPrice + @"
                    ," + RealCost + @"
                    ," + WriteDate + @"
                    ," + StartTime + @"
                    ," + EndTime + @"
                    ,getdate()
                    ,0
                    ," + ImportCoefficient + @"
                    ,NULL
                    ,NULL
                    ,0
                    ,0
                    ,NULL
                    ,0
                    ,0
                    ,NULL
                    );";
                    cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
                    int count = helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
                    if (count > 0)
                    {
                        data = ImportFee.GetImportFee(ID, helper);
                    }
                }
            }
            #endregion
            #region 历史单据还原
            if (data == null)
            {
                var roomfee = RoomFeeHistory.GetRoomFeeHistoryByImportFeeID(ID, helper);
                if (roomfee != null)
                {
                    decimal EndPoint          = roomfee.UseCount > 0 ? roomfee.UseCount : 0;
                    decimal UnitPrice         = roomfee.UnitPrice > 0 ? roomfee.UnitPrice : 0;
                    decimal RealCost          = roomfee.RealCost > 0 ? roomfee.RealCost : 0;
                    string  WriteDate         = "'" + (roomfee.RoomFeeWriteDate > DateTime.MinValue ? roomfee.RoomFeeWriteDate.ToString("yyyy-MM-dd") : DateTime.Now.ToString("yyyy-MM-dd")) + "'";
                    string  StartTime         = roomfee.StartTime > DateTime.MinValue ? "'" + roomfee.StartTime.ToString("yyyy-MM-dd") + "'" : "NULL";
                    string  EndTime           = roomfee.EndTime > DateTime.MinValue ? "'" + roomfee.EndTime.ToString("yyyy-MM-dd") + "'" : "NULL";
                    decimal ImportCoefficient = roomfee.RoomFeeCoefficient > 0 ? roomfee.RoomFeeCoefficient : 0;
                    string  cmdtext           = string.Empty;
                    cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
                    cmdtext += @"insert into [ImportFee]
                    ([ID]
                    ,[RoomID]
                    ,[ChargeDate]
                    ,[ChargeID]
                    ,[StartPoint]
                    ,[EndPoint]
                    ,[TotalPoint]
                    ,[UnitPrice]
                    ,[TotalPrice]
                    ,[WriteDate]
                    ,[StartTime]
                    ,[EndTime]
                    ,[AddTime]
                    ,[ChargeStatus]
                    ,[ImportCoefficient]
                    ,[ImportBiaoCategory]
                    ,[ImportBiaoName]
                    ,[ChargeBiaoID]
                    ,[ProjectBiaoID]
                    ,[ImportBiaoGuiGe]
                    ,[ImportRate]
                    ,[ImportReducePoint]
                    ,[ImportChargeRoomNo])
                    values( 
                     " + ID + @"
                    ," + roomfee.RoomID + @"
                    ,NULL
                    ," + roomfee.ChargeID + @"
                    ,0
                    ," + EndPoint + @"
                    ," + EndPoint + @"
                    ," + UnitPrice + @"
                    ," + RealCost + @"
                    ," + WriteDate + @"
                    ," + StartTime + @"
                    ," + EndTime + @"
                    ,getdate()
                    ,0
                    ," + ImportCoefficient + @"
                    ,NULL
                    ,NULL
                    ,0
                    ,0
                    ,NULL
                    ,0
                    ,0
                    ,NULL
                    );";
                    cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
                    int count = helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
                    if (count > 0)
                    {
                        data = ImportFee.GetImportFee(ID, helper);
                    }
                }
            }
            #endregion
            return(data);
        }
        private void importgongtan(HttpContext context)
        {
            HttpFileCollection uploadFiles = context.Request.Files;

            if (uploadFiles.Count == 0)
            {
                context.Response.Write("请选择一个文件");
                return;
            }
            if (string.IsNullOrEmpty(uploadFiles[0].FileName))
            {
                context.Response.Write("请选择一个文件");
                return;
            }
            string msg          = string.Empty;
            int    CompanyID    = WebUtil.GetCompanyID(context);
            int    CreatorID    = WebUtil.GetUser(context).UserID;
            string AddMan       = WebUtil.GetUser(context).RealName;
            bool   ImportFailed = false;
            var    summarylist  = ChargeSummary.GetChargeSummaries().ToArray();

            titleList = Foresight.DataAccess.TableColumn.GetTableColumnByPageCode("roomfeesource", true).Where(p => !p.ColumnName.Equals("选择按钮")).ToArray();
            using (SqlHelper helper = new SqlHelper())
            {
                try
                {
                    helper.BeginTransaction();
                    #region 导入处理
                    for (int j = 0; j < uploadFiles.Count; j++)
                    {
                        HttpPostedFile postedFile = uploadFiles[j];
                        string         filepath   = HttpContext.Current.Server.MapPath("~/upload/ImportChaoBiao/" + DateTime.Now.ToString("yyyyMMdd"));
                        if (!System.IO.Directory.Exists(filepath))
                        {
                            System.IO.Directory.CreateDirectory(filepath);
                        }
                        string filename = DateTime.Now.ToLocalTime().ToString("yyyyMMddHHmmss") + "_" + postedFile.FileName;
                        string fullpath = Path.Combine(filepath, filename);
                        postedFile.SaveAs(fullpath);
                        DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            Project project = null;
                            object  Value, FullName, RoomName;
                            if (GetColumnValue("房源信息", table, i, out FullName) && GetColumnValue("房间号", table, i, out RoomName))
                            {
                                project = Project.GetProjectByFullName(RoomName.ToString(), FullName.ToString(), CompanyID, helper);
                            }
                            if (project == null)
                            {
                                msg         += "<p>第" + (i + 2) + "行上传失败。原因:房间资源不存在</p>";
                                ImportFailed = true;
                                break;
                            }
                            ChargeSummary summary = null;
                            if (GetColumnValue("收费项目", table, i, out Value))
                            {
                                summary = summarylist.FirstOrDefault(p => p.CompanyID == CompanyID && p.Name.Equals(Value.ToString()));
                            }
                            if (summary == null)
                            {
                                msg         += "<p>第" + (i + 2) + "行上传失败。原因:收费项目项目不存在</p>";
                                ImportFailed = true;
                                break;
                            }
                            int ID = 0;
                            int.TryParse(table.Rows[i]["账单ID"].ToString(), out ID);
                            Foresight.DataAccess.ImportFee importFee = null;
                            if (ID > 0)
                            {
                                importFee = Foresight.DataAccess.ImportFee.GetOrCreateImportFeeByID(ID, helper, CanCreate: false);
                            }
                            if (importFee == null)
                            {
                                importFee = new Foresight.DataAccess.ImportFee();
                                importFee.ChargeStatus = 0;
                                importFee.AddTime      = DateTime.Now;
                                importFee.ChargeID     = summary.ID;
                                importFee.RoomID       = project.ID;
                                importFee.ChargeStatus = 2;
                            }
                            if (importFee.ChargeStatus == 1)
                            {
                                msg         += "<p>第" + (i + 2) + "行上传失败。原因:该费用已收取</p>";
                                ImportFailed = true;
                                break;
                            }
                            if (GetColumnValue("上次读数", table, i, out Value))
                            {
                                importFee.StartPoint = GetDecimalValue(Value);
                            }
                            if (GetColumnValue("本次读数", table, i, out Value))
                            {
                                importFee.EndPoint = GetDecimalValue(Value);
                            }
                            decimal totalpoint = decimal.MinValue;
                            if (GetColumnValue("用量", table, i, out Value))
                            {
                                totalpoint = GetDecimalValue(Value);
                            }
                            if (totalpoint == decimal.MinValue)
                            {
                                totalpoint = (importFee.EndPoint == decimal.MinValue ? 0 : importFee.EndPoint) - (importFee.StartPoint == decimal.MinValue ? 0 : importFee.StartPoint);
                            }
                            totalpoint           = totalpoint < 0 ? 0 : totalpoint;
                            importFee.TotalPoint = totalpoint;
                            if (GetColumnValue("单价", table, i, out Value))
                            {
                                importFee.UnitPrice = GetDecimalValue(Value);
                            }
                            if (GetColumnValue("系数", table, i, out Value))
                            {
                                importFee.ImportCoefficient = GetDecimalValue(Value);
                            }
                            decimal totalprice = decimal.MinValue;
                            if (GetColumnValue("金额", table, i, out Value))
                            {
                                totalprice = GetDecimalValue(Value);
                            }
                            if (totalprice == decimal.MinValue)
                            {
                                totalprice = (importFee.TotalPoint == decimal.MinValue ? 0 : importFee.TotalPoint) * (importFee.ImportCoefficient == decimal.MinValue ? 0 : importFee.ImportCoefficient) * (importFee.UnitPrice == decimal.MinValue ? 0 : importFee.UnitPrice);
                            }
                            importFee.TotalPrice = totalprice;
                            if (GetColumnValue("收费状态", table, i, out Value))
                            {
                                importFee.ChargeStatus = Value.ToString().Equals("已收") ? 1 : (Value.ToString().Equals("未收") ? 0 : 2);
                            }
                            if (GetColumnValue("账单日期", table, i, out Value))
                            {
                                importFee.WriteDate = GetDateTimeValue(Value);
                            }
                            if (GetColumnValue("计费开始日期", table, i, out Value))
                            {
                                importFee.StartTime = GetDateTimeValue(Value);
                            }
                            if (GetColumnValue("计费结束日期", table, i, out Value))
                            {
                                importFee.EndTime = GetDateTimeValue(Value);
                            }

                            string ImportBiaoCategory = string.Empty;
                            string ImportBiaoName     = project.Name;
                            string ImportChargeRoomNo = string.Empty;
                            if (GetColumnValue("表种类", table, i, out Value))
                            {
                                ImportBiaoCategory = Value.ToString();
                            }
                            if (GetColumnValue("表名称", table, i, out Value))
                            {
                                ImportBiaoName = Value.ToString();
                            }
                            if (GetColumnValue("缴费户号", table, i, out Value))
                            {
                                ImportChargeRoomNo = Value.ToString();
                            }
                            Foresight.DataAccess.ImportFee lastimportFee = Foresight.DataAccess.ImportFee.GetImportFeeByRoomID(project.ID, null, importFee.ChargeID, helper);
                            if (string.IsNullOrEmpty(ImportBiaoCategory))
                            {
                                ImportBiaoCategory = summary.BiaoCategory;
                                if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportBiaoCategory))
                                {
                                    ImportBiaoCategory = lastimportFee.ImportBiaoCategory;
                                }
                            }
                            if (string.IsNullOrEmpty(ImportBiaoName))
                            {
                                ImportBiaoName = project.Name;
                                if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportBiaoName))
                                {
                                    ImportBiaoName = lastimportFee.ImportBiaoName;
                                }
                            }
                            if (string.IsNullOrEmpty(ImportChargeRoomNo))
                            {
                                if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportChargeRoomNo))
                                {
                                    ImportChargeRoomNo = lastimportFee.ImportChargeRoomNo;
                                }
                            }
                            importFee.ImportBiaoCategory = ImportBiaoCategory;
                            importFee.ImportBiaoName     = ImportBiaoName;
                            importFee.ImportChargeRoomNo = ImportChargeRoomNo;
                            importFee.Save(helper);
                            if (importFee.ChargeStatus == 0)
                            {
                                SaveRoomFee(importFee, summary.ID, helper);
                            }
                            else if (importFee.ChargeStatus == 1)
                            {
                                SaveRoomHistoryFee(importFee, summary.ID, helper, AddMan);
                            }
                        }
                    }
                    #endregion
                    if (!ImportFailed)
                    {
                        helper.Commit();
                        msg += "<p>导入完成</p>";
                    }
                    else
                    {
                        helper.Rollback();
                        msg += "<p>导入失败</p>";
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.WriteError("ImportGongTanHandler", "visit: importgongtan", ex);
                    helper.Rollback();
                }
                context.Response.Write(msg);
            }
        }