public static int Work(string kstime, string jstime)
{
if (!SqlHelper.TestConnection(SourceConn))
{
log4net.LogManager.GetLogger("logger").Error(string.Format("提供数据库链接【{0}】连接失败！", SourceConn));
throw new Exception(string.Format("提供数据库链接【{0}】连接失败！", SourceConn));
}
const string sourceTableName = "T_Mat_KFPurchase";
const string sourceDetialTableName = "T_Mat_KFPurchaseDetail";
CommonFunction.Initalize(SourceConn, sourceTableName);
var headliList = new List <ICStockBill>();
var bodyliList = new List <ICStockBillEntry>();
var recordlist = new List <string>();
var headsqlstring =
string.Format(@"select a.FBillNo 单据号,a.FBillDate 操作时间,a.FSupplierID 供应商,c.FNumber 仓库,b.FNumber 操作人,a.FKFPurchaseID
from T_Mat_KFPurchase a
left join T_Sys_User b on b.FUserID = a.FBillUserID
left join T_Sys_Group c on c.FGroupID = a.FKFGroupID
where kindeestate is null and a.FBillDate>='{0}' and a.FBillDate<='{1}'",
kstime, jstime);
var bodysqlstring = @"select b.FNumber 药品ID,c.FNumber 仓库,a.FQuantity 数量,a.FPurchaseAmt 进货总价,a.FPurchasePrice 进货单价,a.FKFUnit 药库单位,a.FKFPurchaseID,a.FKFPurchaseDetailID
from T_Mat_KFPurchaseDetail a
left join T_Biz_MedSpec b on b.FFeeItemID=a.FFeeItemID
left join T_Sys_Group c on c.FGroupID = a.FKFGroupID";
var headtable = SqlHelper.Query(SourceConn, headsqlstring, true);
var bodytable = SqlHelper.Query(SourceConn, bodysqlstring);
var i = 1;
var number = CommonFunction.GetMaxNum(RelatedConn, ICStockBill.TableName);
foreach (DataRow itemRow in headtable.Rows)
{
if (LoggerHelper.CheckValue(sourceTableName, itemRow, "FKFPurchaseID", "供应商", "操作人", "仓库"))
{
continue;
}
i = i + 1;
Head head = new Head
{
FBillNo = itemRow["仓库"].ToString().Substring(4, 3) + itemRow["单据号"],//大连医卫设计是同一个药库的BillNo不重复
Fdate = DateTime.Parse(itemRow["操作时间"].ToString()),
FSupplyID = itemRow["供应商"].ToString(),
FSManagerID = itemRow["操作人"].ToString(),
FFManagerID = itemRow["操作人"].ToString(),
FEmpID = itemRow["操作人"].ToString(),
FDCStockID = itemRow["仓库"].ToString(),
FInterID = number + i
};
headliList.Add(head);
recordlist.Add(string.Format("update T_Mat_KFPurchase set kindeestate='1' where FKFPurchaseID='{0}'", itemRow["FKFPurchaseID"]));
var j = 1;
foreach (DataRow bodyitemRow in bodytable.Select(string.Format("FKFPurchaseID='{0}'", itemRow["FKFPurchaseID"])))
{
if (LoggerHelper.CheckValue(sourceDetialTableName, bodyitemRow, "FKFPurchaseDetailID", "药品ID", "仓库"))
{
continue;
}
Body body = new Body
{
FItemID = bodyitemRow["药品ID"].ToString(),
FInterID = head.FInterID,
FQty = bodyitemRow["数量"].ToString(),
Fauxqty = bodyitemRow["数量"].ToString(),
Famount = bodyitemRow["进货总价"].ToString(),
Fauxprice = bodyitemRow["进货单价"].ToString(),
FDCStockID = bodyitemRow["仓库"].ToString(),
FUnitID = bodyitemRow["药库单位"].ToString(),
FEntryID = j
};
bodyliList.Add(body);
j = j + 1;
}
}
var headsqlstringlist = CommonFunction.GetSqlList(RelatedConn, headliList, ICStockBill.TableName);
var bodysqlstringlist = CommonFunction.GetSqlList(RelatedConn, bodyliList, ICStockBillEntry.TableName);
headsqlstringlist.AddRange(bodysqlstringlist);
var resultnumber = SqlHelper.ExecuteSqlTran(RelatedConn, headsqlstringlist);
SqlHelper.ExecuteSqlTran(SourceConn, recordlist);
CommonFunction.UpdateMaxNum(RelatedConn, ICStockBill.TableName, number + i);
return(resultnumber);
}
public static int Work(string kstime, string jstime)
{
const string sourceTableName = "T_Med_MZSale";
const string sourceDetialTableName = "T_Med_MZSaleDetail";
CommonFunction.Initalize(SourceConn, sourceTableName);
var headliList = new List <ICStockBill>();
var bodyliList = new List <ICStockBillEntry>();
var recordlist = new List <string>();
var headsqlstring = string.Format(@"select t1.FMZSaleID,t8.FMZNO as 门诊号,t1.FBillDate as 录入时间,t7.FNumber 录入人,t6.FNumber 科室id,
case
when t3.FBillUser is not null then t3.FBillUser
else (select a1.FBillUser from t_FEE_MZFEEINFO a1 where a1.FMZFEEINFOID=t2.FOriginalFeeInfoID)
end 医师
from T_Med_MZSale t1
left join t_MED_MZSALEDETAIL t2 on t2.FMZSaleID=t1.FMZSaleID
left join t_FEE_MZFEEINFO t3 on t3.FMZFEEINFOID=t2.FMZFEEINFOID
left join T_Sys_Group t6 on t6.FGroupID = t1.FRecvGroupID
left join T_SYS_USER t7 on t7.FUSERID=t1.FBillUserID
join T_BIZ_PATIENT t8 on t8.FPATIENTID=t2.FPATIENTID
where t1.kindeestate is null and t1.FBillDate>='{0}' and t1.FBillDate<='{1}'", kstime, jstime);
var bodysqlstring = @"select b.FNumber as 收费项目id,FQuantity as 实发数量,a.FUnit as 最小单位,a.FCheckPrice as 新单价,a.FCheckAmt as 新总价格,c.FNumber as 发药库房,a.FMZSaleDetailID, a.FMZSaleID
from T_Med_MZSaleDetail a
left join T_Biz_MedSpec b on b.FFeeItemID=a.FFeeItemID
left join T_Sys_Group c on c.FGroupID = a.FSendGroupID";
var headtable = SqlHelper.Query(SourceConn, headsqlstring, true);
var bodytable = SqlHelper.Query(SourceConn, bodysqlstring);
var i = 0;
var number = CommonFunction.GetMaxNum(RelatedConn, ICStockBill.TableName);
foreach (DataRow itemRow in headtable.Rows)
{
if (LoggerHelper.CheckValue(sourceTableName, itemRow, "FMZSaleID", "录入人", "科室id"))
{
continue;
}
i = i + 1;
Head head = new Head
{
FBillNo = itemRow["门诊号"].ToString(),
Fdate = DateTime.Parse(itemRow["录入时间"].ToString()),
FDeptID = itemRow["科室id"].ToString(),
FEmpID = itemRow["录入人"].ToString(),
FSupplyID = itemRow["科室id"].ToString(),
FHeadSelfB0154 = itemRow["医师"].ToString(),
FInterID = number + i
};
headliList.Add(head);
recordlist.Add(string.Format("update T_Med_MZSale set kindeestate='1' where FMZSaleID='{0}'", itemRow["FMZSaleID"]));
var j = 1;
foreach (DataRow bodyitemRow in bodytable.Select(string.Format("FMZSaleID='{0}'", itemRow["FMZSaleID"])))
{
if (LoggerHelper.CheckValue(sourceDetialTableName, bodyitemRow, "FMZSaleDetailID", "发药库房"))
{
continue;
}
Body body = new Body
{
FItemID = bodyitemRow["收费项目id"].ToString(),
FQty = bodyitemRow["实发数量"].ToString() == "" ? "0" : bodyitemRow["实发数量"].ToString(),
Fauxqty = bodyitemRow["实发数量"].ToString() == "" ? "0" : bodyitemRow["实发数量"].ToString(),
FUnitID = bodyitemRow["最小单位"].ToString(),
FConsignPrice = bodyitemRow["新单价"].ToString() == "" ? "0" : bodyitemRow["新单价"].ToString(),
FConsignAmount = bodyitemRow["新总价格"].ToString() == "" ? "0" : bodyitemRow["新总价格"].ToString(),
FDCStockID = bodyitemRow["发药库房"].ToString(),
FInterID = head.FInterID,
FEntryID = j
};
//if (bodyitemRow["最小单位"].ToString() == "g" && bodyitemRow["单位"].ToString() == "kg")
//{
// body.FQty = (Convert.ToDouble(body.FQty) / 1000).ToString(CultureInfo.InvariantCulture);
//}
bodyliList.Add(body);
j = j + 1;
}
}
var headsqlstringlist = CommonFunction.GetSqlList(RelatedConn, headliList, ICStockBill.TableName);
var bodysqlstringlist = CommonFunction.GetSqlList(RelatedConn, bodyliList, ICStockBillEntry.TableName);
headsqlstringlist.AddRange(bodysqlstringlist);
var resultnumber = SqlHelper.ExecuteSqlTran(RelatedConn, headsqlstringlist);
SqlHelper.ExecuteSqlTran(SourceConn, recordlist);
CommonFunction.UpdateMaxNum(RelatedConn, ICStockBill.TableName, number + i);
return(resultnumber);
}
public static int Work(string kstime, string jstime)
{
const string sourceTableName = "T_Mat_KFExport";
const string sourceDetialTableName = "T_Mat_KFExportDetail";
CommonFunction.Initalize(SourceConn, sourceTableName);
var headliList = new List <ICStockBill>();
var bodyliList = new List <ICStockBillEntry>();
var recordlist = new List <string>();
var headsqlstring = string.Format(@"select a.FBillNo as 单据号,a.FBillDate as 操作时间,a.FAppGroupID as 领用部门,c.FNumber as 接收科室,b.FNumber as zhidanren,0 as chukuleixing,a.FKFExportID
from T_Mat_KFExport a
left join T_Sys_User b on b.FUserID = a.FBillUserID
left join T_Sys_Group c on c.FGroupID = a.FKFGroupID where a.FBillDate>='{0}' and a.FBillDate<='{1}' and a.kindeestate is null", kstime, jstime);
var bodysqlstring = @"select b.FNumber as 药品ID,a.FExpQuanlity as 数量,a.FUnit as 药库单位,c.FNumber as 仓库,a.KFExportDetailID,a.FKFExportID
from T_Mat_KFExportDetail as a
left join T_Biz_MedSpec b on b.FFeeItemID=a.FFeeItemID
left join T_Sys_Group c on c.FGroupID = a.FKFGroupID";
var headtable = SqlHelper.Query(SourceConn, headsqlstring, true);
var bodytable = SqlHelper.Query(SourceConn, bodysqlstring);
var i = 0;
var number = CommonFunction.GetMaxNum(RelatedConn, ICStockBill.TableName);
foreach (DataRow itemRow in headtable.Rows)
{
if (LoggerHelper.CheckValue(sourceTableName, itemRow, "FKFExportID", "zhidanren", "chukuleixing"))
{
continue;
}
i = i + 1;
Head head = new Head
{
FBillNo = itemRow["单据号"].ToString(),
Fdate = DateTime.Parse(itemRow["操作时间"].ToString()),
//FDeptID = itemRow["领用部门"].ToString(),
//FSupplyID = itemRow["接收科室"].ToString(),
FFManagerID = itemRow["zhidanren"].ToString(),
FSManagerID = itemRow["zhidanren"].ToString(),
FBillTypeID = itemRow["chukuleixing"].ToString(),
FInterID = number + i
};
headliList.Add(head);
recordlist.Add(string.Format("update T_Mat_KFExport set kindeestate='1' where FKFExportID='{0}'", itemRow["FKFExportID"]));
var j = 1;
foreach (DataRow bodyitemRow in bodytable.Select(string.Format("FKFExportID='{0}'", itemRow["FKFExportID"])))
{
if (LoggerHelper.CheckValue(sourceDetialTableName, bodyitemRow, "KFExportDetailID", "药品ID", "仓库"))
{
continue;
}
Body body = new Body
{
FItemID = bodyitemRow["药品ID"].ToString(),
Fauxqty = bodyitemRow["数量"].ToString() == "" ? "0" : bodyitemRow["数量"].ToString(),
FQty = bodyitemRow["数量"].ToString() == "" ? "0" : bodyitemRow["数量"].ToString(),
FUnitID = bodyitemRow["药库单位"].ToString(),
FDCStockID = bodyitemRow["仓库"].ToString(),
FInterID = head.FInterID,
FEntryID = j
};
bodyliList.Add(body);
j = j + 1;
}
}
var headsqlstringlist = CommonFunction.GetSqlList(RelatedConn, headliList, ICStockBill.TableName);
var bodysqlstringlist = CommonFunction.GetSqlList(RelatedConn, bodyliList, ICStockBillEntry.TableName);
headsqlstringlist.AddRange(bodysqlstringlist);
var resultnumber = SqlHelper.ExecuteSqlTran(RelatedConn, headsqlstringlist);
SqlHelper.ExecuteSqlTran(SourceConn, recordlist);
CommonFunction.UpdateMaxNum(RelatedConn, ICStockBill.TableName, number + i);
return(resultnumber);
}
public static int Work(string kstime, string jstime)
{
const string sourceTableName = "T_Biz_Pharmaceutical";
const string sourceDetialTableName = "T_Biz_PharmaceuticalDetail";
CommonFunction.Initalize(SourceConn, "T_Biz_Pharmaceutical");
var headliList = new List <ICStockBill>();
var bodyliList = new List <ICStockBillEntry>();
var recordlist = new List <string>();
var headsqlstring = string.Format(
@"select a.FPharmaceuticalID 单据号,a.FCreateDate 操作时间,b.FNumber 操作人,a.PharmaceuticalID
from T_Biz_Pharmaceutical a
left join T_Sys_User b on b.FUserID = a.FCreateUserID
where kindeestate is null and a.FCreateDate>='{0}' and a.FCreateDate<='{1}'", kstime, jstime);
var bodysqlstring = @"select b.FNumber 药品ID,a.FQuantity 数量,a.FKFUnit 药库单位,a.PharmaceuticalDetailID,a.FPharmaceuticalID
from T_Biz_PharmaceuticalDetail a
left join T_Biz_MedSpec b on b.FFeeItemID=a.FFeeItemID ";
var headtable = SqlHelper.Query(SourceConn, headsqlstring, true);
var bodytable = SqlHelper.Query(SourceConn, bodysqlstring);
var i = 0;
var number = CommonFunction.GetMaxNum(RelatedConn, ICStockBill.TableName);
foreach (DataRow itemRow in headtable.Rows)
{
if (LoggerHelper.CheckValue(sourceTableName, itemRow, "FPharmaceuticalID", "接收科室", "操作人"))
{
continue;
}
i = i + 1;
Head head = new Head
{
FBillNo = itemRow["单据号"].ToString(),
Fdate = DateTime.Parse(itemRow["操作时间"].ToString()),
FDeptID = itemRow["接收科室"].ToString(),
FSManagerID = itemRow["操作人"].ToString(),
FFManagerID = itemRow["操作人"].ToString(),
FInterID = number + i
};
headliList.Add(head);
recordlist.Add(string.Format("update T_Biz_Pharmaceutical set kindeestate='1' where PharmaceuticalID='{0}'", head.FBillNo));
var j = 1;
foreach (DataRow bodyitemRow in bodytable.Select(string.Format("PharmaceuticalID='{0}'", head.FBillNo)))
{
if (LoggerHelper.CheckValue(sourceDetialTableName, bodyitemRow, "PharmaceuticalDetailID", "药品ID"))
{
continue;
}
Body body = new Body
{
FItemID = bodyitemRow["药品ID"].ToString(),
FQty = bodyitemRow["数量"].ToString(),
Fauxqty = bodyitemRow["数量"].ToString(),
FUnitID = bodyitemRow["药库单位"].ToString(),
//FSCStockID = itemRow["仓库"].ToString(),
FInterID = head.FInterID,
FEntryID = j
};
bodyliList.Add(body);
j = j + 1;
}
}
var headsqlstringlist = CommonFunction.GetSqlList(RelatedConn, headliList, ICStockBill.TableName);
var bodysqlstringlist = CommonFunction.GetSqlList(RelatedConn, bodyliList, ICStockBillEntry.TableName);
headsqlstringlist.AddRange(bodysqlstringlist);
var resultnumber = SqlHelper.ExecuteSqlTran(RelatedConn, headsqlstringlist);
SqlHelper.ExecuteSqlTran(SourceConn, recordlist);
CommonFunction.UpdateMaxNum(RelatedConn, ICStockBill.TableName, number + i);
return(resultnumber);
}