213 lines
11 KiB
C#
213 lines
11 KiB
C#
using Dapper;
|
|
using dccdc.Models;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace dccdc.DAL
|
|
{
|
|
public class invoice_printDal
|
|
{
|
|
public List<invoice_printModel> GetAllList(string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string param = "";
|
|
if (id != "")
|
|
{
|
|
param += " and id=@id";
|
|
}
|
|
|
|
string sql = "select * from invoice_print where 1=1 ";
|
|
return conn.Query<invoice_printModel>(sql + param, new { id = id }).ToList();
|
|
}
|
|
}
|
|
|
|
public List<charge_project_detailModel> GetDetailList(string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from charge_project_detail where 1=1 and invoice_print_id=@id";
|
|
return conn.Query<charge_project_detailModel>(sql, new { id = id }).ToList();
|
|
}
|
|
}
|
|
|
|
public object GetAllList2(string start, string end, string jfbm, string where,string truename,string hstart,string hend)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string param = "";
|
|
if (jfbm != "")
|
|
{
|
|
param += " and department=@jfbm";
|
|
}
|
|
if (where != "")
|
|
{
|
|
param += " and (invoice_num like @where or charge_person_name like @where or company like @where or serial_number like @where )";
|
|
}
|
|
if (truename != "")
|
|
{
|
|
param += " and charge_person_name=@truename";
|
|
}
|
|
if(!string.IsNullOrEmpty(hstart))
|
|
{
|
|
param += " and invoice_num>=@hstart";
|
|
}
|
|
if (!string.IsNullOrEmpty(hend))
|
|
{
|
|
param += " and invoice_num<=@hend";
|
|
}
|
|
DateTime dtend=DateTime.Now;
|
|
DateTime dtstart = DateTime.Now;
|
|
if (!string.IsNullOrEmpty(start))
|
|
{
|
|
// and invoice_date<= @end
|
|
|
|
if(DateTime.TryParse(start,out dtstart))
|
|
{
|
|
param += " and invoice_date >= @start";
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(end))
|
|
{
|
|
// and invoice_date<= @end
|
|
|
|
if (DateTime.TryParse(end, out dtend))
|
|
{
|
|
param += " and invoice_date<= @end";
|
|
}
|
|
}
|
|
param += " order by id desc";
|
|
string sql = "select * from invoice_print where 1=1 ";
|
|
return conn.Query(sql + param, new { start = dtstart, end = dtend, jfbm = jfbm, where = "%" + where + "%", truename = truename,hstart,hend }).ToList();
|
|
}
|
|
}
|
|
|
|
public List<string> GetPerson()
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select distinct charge_person_name from invoice_print where 1=1";
|
|
return conn.Query<string>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public object cancelInvoice(string id, ERPUser user)
|
|
{
|
|
invoice_printModel result;
|
|
charge_maintModel result2;
|
|
List<charge_project_detailModel> models;
|
|
string fph1;
|
|
string fph2;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
result = conn.Query<invoice_printModel>("select * from invoice_print where 1=1 and id=@id ", new { id = id }).FirstOrDefault();
|
|
//发票段表
|
|
result2 = conn.Query<charge_maintModel>("select * from charge_maint where 1=1 and id=@fpdid", new { fpdid = result.fpdid }).FirstOrDefault();
|
|
//明细
|
|
models = conn.Query<charge_project_detailModel>("select * from charge_project_detail where 1=1 and invoice_print_id=@invoice_print_id", new { invoice_print_id = id }).ToList();
|
|
//创建负发票
|
|
string fphs = new chargeDal().getInvoice(user.TrueName, result.print_type);
|
|
if (!fphs.Contains("|"))
|
|
{
|
|
return new { State = 0, Message = fphs };
|
|
}
|
|
fph1 = fphs.Split('|')[0];
|
|
fph2 = fphs.Split('|')[1];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
IDbTransaction transaction = conn.BeginTransaction();
|
|
try
|
|
{
|
|
if (result.invoice_date == DateTime.Now.ToString("yyyy-MM-dd"))
|
|
{
|
|
//收费表未打印
|
|
conn.Execute("update charge set status='作废' where id=@id", new { id = result.chargeid }, transaction);
|
|
decimal total_money = result2.total_money - result.invoice_price;
|
|
string normal_cou = (Convert.ToInt32(string.IsNullOrEmpty(result2.normal_cou) ? "0" : result2.normal_cou) - 1).ToString();
|
|
string abnormal_cou = (Convert.ToInt32(string.IsNullOrEmpty(result2.abnormal_cou) ? "0" : result2.abnormal_cou) + 1).ToString();
|
|
//发票段表
|
|
conn.Execute("update charge_maint set total_money=@total_money ,normal_cou=@normal_cou, abnormal_cou=@abnormal_cou where id=@fpdid", new { total_money = total_money, normal_cou = normal_cou, abnormal_cou = abnormal_cou, fpdid = result.fpdid }, transaction);
|
|
//发票表
|
|
conn.Execute("update invoice_print set iscancel='-1' where id=@id", new { id = id }, transaction);
|
|
conn.Execute("update ymdj set jfzt='未缴费' where chargeid=@id", new { id = result.chargeid }, transaction);
|
|
|
|
}
|
|
else
|
|
{
|
|
return new { State = 0, Message = "不能作废非当日发票！" };
|
|
//收费表未打印
|
|
conn.Execute("update charge set isprint='未打印' where id=@id", new { id = result.chargeid }, transaction);
|
|
decimal total_money = result2.total_money - result.invoice_price;
|
|
string normal_cou = (Convert.ToInt32(string.IsNullOrEmpty(result2.normal_cou) ? "0" : result2.normal_cou) - 1).ToString();
|
|
string abnormal_cou = (Convert.ToInt32(string.IsNullOrEmpty(result2.abnormal_cou) ? "0" : result2.abnormal_cou) + 1).ToString();
|
|
//发票段表
|
|
conn.Execute("update charge_maint set total_money=@total_money ,normal_cou=@normal_cou, abnormal_cou=@abnormal_cou where id=@fpdid", new { total_money = total_money, normal_cou = normal_cou, abnormal_cou = abnormal_cou, fpdid = result.fpdid }, transaction);
|
|
//发票表
|
|
conn.Execute("update invoice_print set iscancel='0' where id=@id", new { id = id }, transaction);
|
|
|
|
result.invoice_date = DateTime.Now.ToString("yyyy-MM-dd");
|
|
result.pay_date = DateTime.Now.ToString("yyyy-MM-dd");
|
|
result.invoice_num2 = result.invoice_num;
|
|
result.invoice_num = fph1;
|
|
result.fpdid = Convert.ToInt32(fph2);
|
|
result.invoice_price = -result.invoice_price;
|
|
result.actually_pay = -result.actually_pay;
|
|
string sql1 = @"INSERT INTO [dbo].[invoice_print]
|
|
([invoice_num],[is_print_price_count],[company] ,[payment_type],[department] ,[charge_person_name],[description],[person_count] ,[match_person_count],[invoice_price],[actually_pay],[invoice_date],[status],[exam_type],[pay_company] ,[pay_date] ,[pay_person],[match_price],[no_match_price],[invoice_type] ,[money_type],[regist_rant],[dept_id],[return_description],[derate_money],[print_type],[accept_status],[serial_number],[upload_status],[clear_num],[chargeid],[fpdid],[invoice_num2],[iscancel])
|
|
VALUES
|
|
(@invoice_num ,@is_print_price_count ,@company ,@payment_type ,@department,@charge_person_name ,@description ,@person_count ,@match_person_count ,@invoice_price ,@actually_pay ,@invoice_date ,@status ,@exam_type ,@pay_company,@pay_date,@pay_person,@match_price ,@no_match_price ,@invoice_type,@money_type ,@regist_rant ,@dept_id,@return_description ,@derate_money
|
|
,@print_type ,@accept_status ,@serial_number ,@upload_status
|
|
,@clear_num,@chargeid,@fpdid,@invoice_num2,@iscancel)select SCOPE_IDENTITY()";
|
|
int newid =conn.Query<int>(sql1, result, transaction).FirstOrDefault();
|
|
|
|
foreach (charge_project_detailModel model in models)
|
|
{
|
|
model.invoice_print_id = newid;
|
|
model.charge_num = -model.charge_num;
|
|
model.charge_price = -model.charge_num;
|
|
}
|
|
string sql2 = @"INSERT INTO [dbo].[charge_project_detail]
|
|
([invoice_print_id],[charge_project],[unit_price],[charge_num],[charge_price])
|
|
VALUES
|
|
(@invoice_print_id,@charge_project,@unit_price,@charge_num,@charge_price)";
|
|
conn.Execute(sql2, models, transaction);
|
|
|
|
}
|
|
transaction.Commit();
|
|
return new { State = 1, Message = "作废成功！" };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
transaction.Rollback();
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public Dictionary<dynamic, dynamic> GetPersonCount(string truename)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select payment_type,sum(invoice_price) as prices from invoice_print where 1=1 and pay_date='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and charge_person_name=@truename and iscancel=1 group by payment_type";
|
|
return conn.Query(sql, new { truename = truename }).ToDictionary(x => x.payment_type.ToString(), x => x.prices.ToString());
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|